Can a foreign key be NULL and/or duplicate?

Short answer: Yes, it can be NULL or duplicate.

I want to explain why a foreign key might need to be null or might need to be unique or not unique. First remember a Foreign key simply requires that the value in that field must exist first in a different table (the parent table). That is all an FK is by definition. Null by definition is not a value. Null means that we do not yet know what the value is.

Let me give you a real life example. Suppose you have a database that stores sales proposals. Suppose further that each proposal only has one sales person assigned and one client. So your proposal table would have two foreign keys, one with the client ID and one with the sales rep ID. However, at the time the record is created, a sales rep is not always assigned (because no one is free to work on it yet), so the client ID is filled in but the sales rep ID might be null. In other words, usually you need the ability to have a null FK when you may not know its value at the time the data is entered, but you do know other values in the table that need to be entered. To allow nulls in an FK generally all you have to do is allow nulls on the field that has the FK. The null value is separate from the idea of it being an FK.

Whether it is unique or not unique relates to whether the table has a one-one or a one-many relationship to the parent table. Now if you have a one-one relationship, it is possible that you could have the data all in one table, but if the table is getting too wide or if the data is on a different topic (the employee – insurance example @tbone gave for instance), then you want separate tables with a FK. You would then want to make this FK either also the PK (which guarantees uniqueness) or put a unique constraint on it.

Most FKs are for a one to many relationship and that is what you get from a FK without adding a further constraint on the field. So you have an order table and the order details table for instance. If the customer orders ten items at one time, he has one order and ten order detail records that contain the same orderID as the FK.

Related Posts:

  1. INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
  2. INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
  3. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  4. Selecting COUNT(*) with DISTINCT
  5. Case in Select Statement
  6. What is a stored procedure?
  7. ORA-00904: invalid identifier
  8. Self Join to get employee manager name
  9. Conversion failed when converting date and/or time from character string while inserting datetime
  10. When should I use CROSS APPLY over INNER JOIN?
  11. Insert Data Into Temp Table with Query
  12. error, string or binary data would be truncated when trying to insert
  13. How to create Temp table with SELECT * INTO tempTable FROM CTE Query
  14. How can I do an UPDATE statement with JOIN in SQL Server?
  15. ORA-00907: missing right parenthesis
  16. Rename column SQL Server 2008
  17. Efficiently convert rows to columns in sql server
  18. Nested select statement in SQL Server
  19. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  20. ORA-01843 not a valid month- Comparing Dates
  21. Drop a temporary table if it exists
  22. How do I escape a single quote in SQL Server?
  23. ORA-00918: column ambiguously defined in SELECT *
  24. How Stuff and ‘For Xml Path’ work in SQL Server?
  25. How do I limit the number of rows returned by an Oracle query after ordering?
  26. Oracle SELECT TOP 10 records
  27. Column name or number of supplied values does not match table definition
  28. Inserting data into a temporary table
  29. Why does NULL = NULL evaluate to false in SQL server
  30. How to Select Top 100 rows in Oracle?
  31. CREATE VIEW must be the only statement in the batch
  32. SQL Server FOR EACH Loop
  33. Best way to do nested case statement logic in SQL Server
  34. The multi-part identifier could not be bound
  35. Get day of week in SQL Server 2005/2008
  36. incorrect syntax error CREATE VIEW must be the only statement in the batch
  37. Oracle Apex column link from LOV values
  38. PLS-00103: Encountered the symbol when expecting one of the following:
  39. ORA-00918: column ambiguously defined in SELECT *
  40. When to use “ON UPDATE CASCADE”
  41. ORA-01861: literal does not match format string
  42. SQL Server Insert if not exists
  43. SQL Server: Invalid Column Name
  44. Is there a combination of “LIKE” and “IN” in SQL?
  45. What is it exactly a BLOB in a DBMS context
  46. How to parse XML data in SQL server table
  47. How do you create a temporary table in an Oracle database?
  48. SQL Server IF NOT EXISTS Usage?
  49. String or binary data would be truncated. The statement has been terminated
  50. How to insert date values into table
  51. Oracle SQL: Update a table with data from another table
  52. Error converting data type varchar to float
  53. How to concatenate text from multiple rows into a single text string in SQL Server
  54. Count(*) vs Count(1) – SQL Server
  55. What is the Oracle equivalent of SQL Server’s IsNull() function?
  56. I want to use CASE statement to update some records in sql server 2005
  57. SELECT DISTINCT on one column
  58. Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (Year)
  59. SQL selecting rows by most recent date with two unique columns
  60. TSQL PIVOT MULTIPLE COLUMNS
  61. Microsoft OLE DB Provider for SQL Server error ‘80004005’
  62. Comparing Dates in Oracle SQL
  63. How do I reset a sequence in Oracle?
  64. SQL “select where not in subquery” returns no results
  65. ListAGG in SQLSERVER
  66. Equivalent of Oracle’s RowID in SQL Server
  67. Is there a Max function in SQL Server that takes two values like Math.Max in .NET?
  68. Subtract one day from datetime
  69. MySQL equivalent of DECODE function in Oracle
  70. Is it possible to specify condition in Count()?
  71. Replacing NULL with 0 in a SQL server query
  72. SQL Server dynamic PIVOT query?
  73. SQL Server: Make all UPPER case to Proper Case/Title Case
  74. How do I query for all dates greater than a certain date in SQL Server?
  75. Backup a single table with its data from a database in sql server 2008
  76. Error converting data type varchar
  77. Temporary table in SQL server causing ‘ There is already an object named’ error
  78. I want to use CASE statement to update some records in sql server 2005
  79. SQL Server “cannot perform an aggregate function on an expression containing an aggregate or a subquery”, but Sybase can
  80. Get everything after and before certain character in SQL Server
  81. Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
  82. Oracle: If Table Exists
  83. How to calculate age (in years) based on Date of Birth and getDate()
  84. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  85. How to create id with AUTO_INCREMENT on Oracle?
  86. LEFT function in Oracle
  87. Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot
  88. How can I group by date time column without taking time into consideration
  89. What are database constraints?
  90. Inner Joining three tables
  91. How do I view the Explain Plan in Oracle Sql developer?
  92. Append table to an existing one: SQL Server
  93. SQL Server – An expression of non-boolean type specified in a context where a condition is expected, near ‘RETURN’
  94. ORA-01779: cannot modify a column which maps to a non key-preserved table
  95. SUM OVER PARTITION BY
  96. Filter data based on date in sql
  97. Update query using Subquery in Sql Server
  98. Varchar invalid for Sum operator
  99. Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF
  100. ora-06553 pls-306 wrong number or types of arguments in call to ‘ogc_x’

Leave a Comment