How to reference other tables in check constraints?

sql-server referential-integrity check-constraints
Jake Petroules picture Jake Petroules · Mar 21, 2011 · Viewed 10.5k times · Source

I have a table, ProductSupportArticles:

ProductSupportArticleID int NOT NULL <primary key>
ParentArticleID int NULL
ProductID int NOT NULL
Title varchar(100) NOT NULL
Content varchar(MAX) NOT NULL

ProductID is a foreign key to Products.ID, ParentArticleID is a foreign key to the same table, ProductSupportArticles.ProductSupportArticleID. I have a check constraint ProductSupportArticleID != ParentArticleID so that an article cannot be its own parent.

However, a support article pertaining to a particular product should not be able to be the parent or child of an article pertaining to a different product. How can I add a check constraint or similar saying: (ProductID = (SELECT ProductID FROM ProductSupportArticles P WHERE ParentArticleID = P.ProductSupportArticleID))

Or how should I implement my tables differently?

Answer

A-K picture A-K · Mar 21, 2011
  1. Create a UNIQUE constraint on (ProductSupportArticleID, ProductID).
  2. Have a FK refer (ParentArticleID, ProductID) to (ProductSupportArticleID, ProductID)

Warning: enforcing business rules via UDFs wrapped in CHECK constraints has multiple loopholes. For example, they may give false positives and false negatives for multi-row modifications. Also they are very slow.

Related questions

SQL Server foreign key to multiple tables

I have the following database schema: members_company1(id, name, ...); members_company2(id, name, ...); profiles(memberid, membertypeid, ...); membertypes(id, name, ...) [ { id : 1, name : 'company1', ... }, { id : 2, name : 'company2', ... } ]; So each profile belongs to a certain member either from company1 or …

Read More
null value in foreign key

I copied the following paragraph from http://msdn.microsoft.com/en-us/library/ms175464(v=sql.105).aspx A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all …

Read More
SQL Server: how to know if any row is referencing the row to delete

You cannot delete a row if any row is referencing the row to delete via a FK. Is it possible to know if any row is referencing the row to delete before executing a DELETE statement?

Read More