How to set a database integrity check on foreign k

问题:

I have four Database Tables like these:

Book
ID_Book |ID_Company|Description

BookExtension
ID_BookExtension | ID_Book| ID_Discount

Discount
ID_Discount | Description | ID_Company

Company
ID_Company | Description

Any BookExtension record via foreign keys points indirectly to two different ID_Company fields:

BookExtension.ID_Book references a Book record that contains a Book.ID_Company
BookExtension.ID_Discount references a Discount record that contains a Discount.ID_Company

Is it possible to enforce in Sql Server that any new record in BookExtension must have Book.ID_Company = Discount.ID_Company ?

In a nutshell I want that the following Query must return 0 record!

SELECT count(*) from BookExtension 
INNER JOIN Book ON BookExstension.ID_Book = Book.ID_Book
INNER JOIN Discount ON BookExstension.ID_Discount = Discount.ID_Discount
WHERE Book.ID_Company <> Discount.ID_Company

or, in plain English:
I don't want that a BookExtension record references a Book record of a Company and a Discount record of another different Company!

回答1:

Unless I've misunderstood your intent, the general form of the SQL statement you'd use is

ALTER TABLE FooExtension
ADD CONSTRAINT your-constraint-name
CHECK (ID_Foo = ID_Bar);

That assumes existing data already conforms to the new constraint. If existing data doesn't conform, you can either fix the data (assuming it needs fixing), or you can limit the scope (probably) of the new constraint by also checking the value of ID_FooExtension. (Assuming you can identify "new" rows by the value of ID_FooExtension.)

Later . . .

Thanks, I did indeed misunderstand your situation.

As far as I know, you can't enforce that constraint the way you want to in SQL Server, because it doesn't allow SELECT queries within a CHECK constraint. (I might be wrong about that in SQL Server 2008.) A common workaround is to wrap a SELECT query in a function, and call the function, but that's not reliable according to what I've learned.

You can do this, though.

1. Create a UNIQUE constraint on Book (ID_Book, ID_Company). Part of it will look like UNIQUE (ID_Book, ID_Company).
2. Create a UNIQUE constraint on Discount (ID_Discount, ID_Company).
3. Add two columns to BookExtension--Book_ID_Company and Discount_ID_Company.
4. Populate those new columns.
5. Change the foreign key constraints in BookExtension. You want BookExtension (ID_Book, Book_ID_Company) to reference Book (ID_Book, ID_Company). Similar change for the foreign key
   referencing Discount.

Now you can add a check constraint to guarantee that BookExtension.Book_ID_Company is the same as BookExtension.Discount_ID_Company.

回答2:

I'm not sure how [in]efficient this would be but you could also use an indexed view to achieve this. It needs a helper table with 2 rows as CTEs and UNION are not allowed in indexed views.

CREATE TABLE dbo.TwoNums
 (
 Num int primary key
 )

 INSERT INTO TwoNums SELECT 1 UNION ALL SELECT 2

Then the view definition

 CREATE VIEW dbo.ConstraintView
 WITH SCHEMABINDING
 AS
    SELECT 1 AS Col FROM dbo.BookExtension 
    INNER JOIN dbo.Book ON dbo.BookExtension.ID_Book = Book.ID_Book
    INNER JOIN dbo.Discount ON dbo.BookExtension.ID_Discount = Discount.ID_Discount
    INNER JOIN dbo.TwoNums ON  Num = Num
    WHERE dbo.Book.ID_Company <> dbo.Discount.ID_Company

And a unique index on the View

CREATE UNIQUE CLUSTERED INDEX [uix] ON [dbo].[ConstraintView]([Col] ASC)