CODEWITHSUNDEEP
sqloracle-databaseforeign-keysuniquenotnull
Zampanò
Zampanò

Reputation: 594

Should Foreign Key Columns be Unique and Not Null?

I understand that, unless specified, foreign key columns can be NULL and duplicated (at least in Oracle SQL). Is it better practice to have foreign key columns declared not null and unique or leave them as is? Is this a decision that should be made based on the situation at hand, or is there a general rule that should be followed?

Upvotes: 3

Views: 12970

Answers (2)

Larry Lustig
Larry Lustig

Reputation: 50970

All databases allow foreign keys to be NULLable and non-UNIQUE. How you choose to declare a particular foreign key depends on the business case.

Consider the following tables used by a company that sells supplies to secret agents.

CountryList (
  CountryCode NOT NULL PRIMARY KEY, 
  CountryName NOT NULL
)
SecretAgents (
  CodeName NOT NULL PRIMARY KEY, 
  HomeCountryCode FOREIGN KEY REFERENCES CountryList(CountryCode)
)

Clearly, HomeCountryCode will not be unique because you may sell to more than one spy in each country. Is it NULLable? That depends on whether your business model requires each customer to declare their home country or not. If the model allows you to do business with someone who does not have a home country, or does not wish to reveal the home country to you, then the field should be NULLable. But if a state-less actor is not contemplated in your business model you should declare the column NOT NULL so that an invalid customer record cannot be created.

Now, consider the additional table

SpyMasters (
  CountryCode NOT NULL PRIMARY KEY References CountryList(CountryCode),
  Name NOT NULL PRIMARY KEY
)

This table lists the (singleton) head of spying for those countries that have a spy master. Not all countries will appear in this list, but each country can appear only once. In this case the CountryCode field is UNIQUE -- but you don't have to declare that explicitly because PRIMARY KEY always includes uniqueness.

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The foreign key is an attribute in another table. In the original table ("referenced table"), the foreign key should be unique and non-NULL. In fact, it should almost always be the primary key of that table.

In the referencing table, the referencing column should only be declared not-NULL if a value is always required. It should only be declared unique if you never want duplicates. In other words, it depends on the characteristics in the referencing table, not the referenced table.

Upvotes: 4

Related Questions