Please clarify two things for me:
Can a Foreign key be NULL? Can a Foreign key be duplicate? As fair as I know, NULL shouldn't be used in foreign keys, but in some application of mine I'm able to input NULL in both Oracle and SQL Server, and I don't know why.
Answers
Yes, let's clarify both aspects:
-
NULL in Foreign Keys:
- In standard SQL, including Oracle and SQL Server, a foreign key column can contain NULL values unless it's explicitly declared as
NOT NULL. - The presence of a NULL value in a foreign key column typically indicates that the relationship represented by that foreign key is optional. In other words, it means that the row in the referencing table does not have to be related to a row in the referenced table.
- However, if the foreign key column is declared as
NOT NULL, then NULL values are not allowed in that column.
- In standard SQL, including Oracle and SQL Server, a foreign key column can contain NULL values unless it's explicitly declared as
-
Duplicate Foreign Key Values:
- In a properly normalized relational database, each foreign key value in a referencing table should correspond to a unique primary or unique key value in the referenced table. This ensures the integrity of the relationships between the tables.
- However, in some cases, it's possible for a foreign key column to contain duplicate values. This could happen due to various reasons, such as denormalized data, poor data quality, or relaxed constraints.
- Whether duplicate foreign key values are allowed depends on the constraints defined on the foreign key column. By default, most relational database systems allow duplicate values in foreign key columns unless you explicitly define a unique constraint or index on the column.
In summary, NULL values are generally allowed in foreign key columns unless the column is explicitly declared as NOT NULL. Duplicate foreign key values may occur in certain situations but are typically avoided to maintain data integrity unless explicitly permitted by the database schema.
