Can a foreign key be NULL and/or duplicate?

Yes foreign key can be null as told above by senior programmers... I would add another scenario where Foreign key will required to be null.... suppose we have tables comments, Pictures and Videos in an application which allows comments on pictures and videos. In comments table we can have two Foreign Keys PicturesId, and VideosId along with the primary Key CommentId. So when you comment on a video only VideosId would be required and pictureId would be null... and if you comment on a picture only PictureId would be required and VideosId would be null...

4 years ago

Foreign keys allow key values that are all NULL, even if there are no matching PRIMARY or UNIQUE keys

No Constraints on the Foreign Key

When no other constraints are defined on the foreign key, any number of rows in the child table can reference the same parent key value. This model allows nulls in the foreign key. ...

NOT NULL Constraint on the Foreign Key

When nulls are not allowed in a foreign key, each row in the child table must explicitly reference a value in the parent key because nulls are not allowed in the foreign key.

Any number of rows in the child table can reference the same parent key value, so this model establishes a one-to-many relationship between the parent and foreign keys. However, each row in the child table must have a reference to a parent key value; the absence of a value (a null) in the foreign key is not allowed. The same example in the previous section can be used to illustrate such a relationship. However, in this case, employees must have a reference to a specific department.

4 years ago

it depends on what role this foreign key plays in your relation.

if this foreign key is also a key attribute in your relation, then it can't be NULL
if this foreign key is a normal attribute in your relation, then it can be NULL.

4 years ago

A foreign key can be set to allow NULL values, but it cannot be set to allow duplicate values. A foreign key is used to establish a link between the data in two tables, and the values in the foreign key column of one table must match the values in the primary key column of another table. Allowing duplicate values in a foreign key would violate the unique constraint of the primary key and would not establish a clear link between the data in the two tables.

2 years ago