Allow constant references in foreign key constraints - by Dimitri Furman

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


7
0
Sign in
to vote
ID 498545 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 10/19/2009 11:39:02 AM
Access Restriction Public

Description

This suggestion is to allow constants, in addition to column names, in the definition of foreign key constraints. For example:

ALTER TABLE Table1
ADD CONSTRAINT fk1 FOREIGN KEY (Col1, 'A') REFERENCES Table2 (Col1, Col2);

One important use case for this is declarative integrity enforcement when modeling entity subtypes. As an example, consider a data model for a store that sells Books, CDs, and DVDs, which are all subtypes of Product. The data model has four tables: Product, Book, CD, DVD. The constraint to be enforced in this case is that a product can be only of one subtype (disjoint subtyping), thus a row in Product can only be referenced by a row in one of the other three tables. With the proposed syntax, it would be possible to to model this as follows:

CREATE TABLE Product (
ProductID int PRIMARY KEY,
ProductType char(1) NOT NULL CHECK (ProductType IN ('B','C','D')),
CONSTRAINT skProduct1 UNIQUE (ProductID, ProductType)
);

CREATE TABLE Book (
BookID int PRIMARY KEY,
CONSTRAINT fkBookProduct FOREIGN KEY (BookID, 'B') REFERENCES Product (ProductID, ProductType)
);

and similar for the two other tables.

Non-disjoint and overlapping subtyping can be modeled similarly, with the required subtyping constraint implemented as a CHECK constraint on the main entity type table.

See http://www.idi.ntnu.no/~heggland/cv/ForeignSuperkeys.pdf for detailed discussion and additional use cases.

Presently, one can only implement this approach to modeling entity subtyping by introducing actual type differentiator columns (perhaps as persisted computed columns) in each table corresponding to a subtype, which is much less efficient and robust compared to the proposed change in allowed T-SQL syntax.
Sign in to post a comment.
Posted by Microsoft on 3/24/2011 at 4:26 PM
Hello Dimitri,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to respond to this message and we will take another look.

Jan
SQL Server Engine
Posted by Dimitri Furman on 1/6/2010 at 7:48 PM
Hi Umachandar,

Thanks for commenting, and thanks for mentioning the solution using a view with CHECK OPTION - it could certainly be useful in some cases.

I agree with you that assertions would be the optimal solution here. I would be delighted if/when they appear in T-SQL, and I can certainly understand why you'd prefer working on such a generic approach vs. doing something to support only one specific case. That said, based on current lack of support for assertions in all major DBMSs, they are obviously not that easy to implement (or perhaps are not that high on the list of priorities), so I can't bet on them being supported in any reasonably near future. My hope in suggesting literals in FK constraints was that this could be a much simpler change, that would add an important case to the number of scenarios where data integrity can be enforced declaratively.
Posted by Microsoft on 1/6/2010 at 3:18 PM
Hi Dimitri,
Thanks for your feedback. You can achieve what you want today by using some of the existing features. See below example. It uses a view with CHECK OPTION as primary mechanism for DML against the Books table. This does require some discipline in terms of using only the view for INSERT/UPDATE/DELETE/MERGE operations. But if you are using SPs primarily as your API this shouldn't be hard to enforce.

CREATE TABLE Product (
ProductID int PRIMARY KEY,
ProductType char(1) NOT NULL CHECK (ProductType IN ('B','C','D')),
CONSTRAINT skProduct1 UNIQUE (ProductID, ProductType)
);

CREATE TABLE Book (
BookID int PRIMARY KEY,
CONSTRAINT fkBookProduct FOREIGN KEY (BookID) REFERENCES Product (ProductID)
);
go
CREATE VIEW dbo.Product_Books
AS
SELECT b.BookID
FROM dbo.Book as b
JOIN dbo.Product as p
    ON p.ProductID = b.BookID
WHERE p.ProductType = 'B'
WITH CHECK OPTION;
go

insert into dbo.Product (ProductID, ProductType) VALUES(1, 'B'), (2, 'C');
go
insert into dbo.Product_Books (BookID) VALUES(1);
go
insert into dbo.Product_Books (BookID) VALUES(2);
go
update dbo.Product_Books set BookID = 2 where BookID = 1;
go
select * from dbo.Product_Books;
go

drop view dbo.Product_Books;
drop table dbo.Book, dbo.Product;
go

Lastly, if we implemented the ANSI SQL feature ASSERTION, then it would become even more easier to enforce such complex constraints declaratively like:

CREATE ASSERTION Product_Books
CHECK (NOT EXISTS(
SELECT *
FROM dbo.Book as b
JOIN dbo.Product as p
    ON p.ProductID = b.BookID
WHERE p.ProductType <> 'B'
));

So the right approach would be to do something like ASSERTION and not extend the FOREIGN KEY syntax.
Thanks again for your request and we will consider it for a future version of SQL Server. Hopefully, we can provide ASSERTION which will enable even more scenarios.

--
Umachandar, SQL Programmability Team