CODEWITHSUNDEEP
sql
Killian
Killian

Reputation: 934

Null value in a Foreign Key

This question has popped up a couple of times, but I'm still not 100% on how it works.

I'm creating some tables for bank infrastructure. This bank provides services to a company. There are groups that exist within the company that can access some or all of these services, depending on permissions. But there are also groups within the bank, that have no relation to the company, that can access these services.

My group table, has an Group_ID(PK), and Company_ID and Bank_ID as (FK). Is it OK for these FKs to be null, depending on whether the group is company group or a bank group?

Upvotes: 3

Views: 405

Answers (4)

HLGEM
HLGEM

Reputation: 96572

I think your basic model is incorrect. You should probably use an overall table (lets call it users for example purposes) that contains a record for anyone who needs to use the system whether banks or or employees or whoever. It should contain and information that is simllar to all types of users. This is the key you would use in the USERGroups table to set them up to particular groups.

Then the company and the bank tables should have an FK to the user table (which are in a one-one relationshship with users and so the value should be both the FK and PK) and taht table coantnas the field that are differnt for each type of entity.

While yes you can have the structre you described, it is hard to enforce that only one of the keys is allowed to be filled in. Typically I would only allow an FK to be null if it is something that might not be known at the time the record is created. For instance in an education setting, you might set up a course schedule for the Spring close to a year ahead but have some classes where the instrucotr for the spring semester has not yet been hired, so the instrutorID would be null.

Upvotes: 0

Unreason
Unreason

Reputation: 12704

It depends what kind of relationship you want to model.

FK can be null if the relationship you are trying to model does not require the existence of a record in a related table (so 0..1 or 0..n can be null while 1..1 or 1..n should not allow it).

Read about referential actions and you will begin to see some other finer details.

Upvotes: 1

Franck Dernoncourt
Franck Dernoncourt

Reputation: 83187

SQL Server 2008 R2:

A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. To make sure that all values of a composite FOREIGN KEY constraint are verified, specify NOT NULL on all the participating columns.

MySQL with InnoDB also supports null values in foreign keys.

Upvotes: 0

Joe Enos
Joe Enos

Reputation: 40393

Nullable foreign keys are fine (at least in SQL Server, not sure about all RDBMSes). It just means the column can be null, but if it's not, then it needs to meet the constraint.

Upvotes: 4

Related Questions