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
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More