Foreign Key Constraint in child table allows to insert values that doesn’t exists in the parent table – This article will take you through the common SQL errors that you might encounter while working with sql, sql-server, sql-server-2012. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
I am using SQL Server 2012. I have defined a foreign key constraint on a table. The foreign key references composite primary key. The fk constraint does not work when one column has ‘00000’ and other column is null. The parent table does not contain ‘00000’. Both of the fk columns have varchar data type.
Below is the example:
INSERT INTO XYZ
([BUSINESS_PARTNER_ID]
,[INDUSTRY_TYPE_CDE]
,[INDUSTRY_SUBTYPE_CDE])
VALUES
(1,
Null
'00000')
GO
The industry type and industry sub type column is referenced from another table.
Below is the script:
ALTER TABLE [nfs].[xyz] WITH NOCHECK
ADD CONSTRAINT [FK_BPMAIN__ITCDE_ISTCDE]
FOREIGN KEY([INDUSTRY_TYPE_CDE], [INDUSTRY_SUBTYPE_CDE])
REFERENCES [nfs].[abc] ([INDUSTRY_TYPE_CDE], [INDUSTRY_SUBTYPE_CDE])
GO
ALTER TABLE [nfs].[xyz]
CHECK CONSTRAINT [FK_BPMAIN__ITCDE_ISTCDE]
GO
SQL Server does not give any error on insertion. It inserts the value in the child table. According to my understanding, the fk columns can be null or should contain values that exist in the parent table.
Solution :
why does the fk constraint isn’t checked when one or more column contains null? Can you please explain this behavior?
First, we have the practical reasons. Foreign keys are maintained and checked using indexes. In order for an index to be usable, we need to know the (sought for) values of all columns within the index. If we have an index/pk on (a,b) and we have a foreign key value of (NULL,1), we cannot seek within the index in order to determine whether there is any row with a b value of 1. This would make the foreign key “expensive” to maintain.
But secondly, we need to consider consistency. For the single column case, its fairly uncontroversial – if you have a value in the FK column, then there needs to be a matching value in the referenced column. Otherwise, if the FK column is NULL then the constraint isn’t checked.
But, how do we extend this to multiple columns? What is the rule above? There’s not a single obvious interpretation, but instead multiple ones. Is the above rule “if all columns are non-NULL, then the constraint is checked” or “if any columns are non-NULL, then the constraint is checked”? These rules are identical when only a single column is under consideration.
You expected the rule to be the second one, when it is in fact the first. This is explicitly documented:
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.
While you really should have provided a complete example as Zohar Peled said, it is likely that your problem lies in the WITH NOCHECK option in the foreign key definition. It should be CHECK to make the foreign key enabled and enforced by the engine. The way it is, the constraint is not trusted.
See What is lost when I create a Foreign Key using WITH NOCHECK for examples what happens when you use this option.
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.