Is it possible to add a logic Constraint to a Fore

问题:

I've got two tables and I've added a foreign key constraint. Kewl - works great. Now, is it possible to further constrain that relationship against some data in the parent table?

Basically, I have animals in the parent table, and for the child table wishto only contain data where the parent data are .... um .. mammals.

eg.

Animals
^^^^^^^
AnimalId INT PK NOT NULL IDENTITY
AnimalType TINYINT NOT NULL -- 1: Mammal, 2:Reptile, etc..
Name

Mammals
^^^^^^^
AnimalId INT PK FK NOT NULL
NumberOfMammaryGlads TINYINT NOT NULL

So, i wishto make sure that the AnimalId can only be of type Animals.AnimalType = 1

Is this possible??

I don't want to allow someone to try and insert some info against a reptile, in the child table...

Cheers :)

Edit:

I thought I had to use a Check Constraint (confirmed below from my first two answers - cheers!), but I wasn't sure how to (eg. the sql syntax to refer to the Animals table).

Update:

Alex has a very good post (below) that benchmarks some of the suggestions.... a very good read!

回答1:

I ran a small benchmark - in this case the approach with a UDF runs almost 100 times slower.

The overhead of an FK in CPU time = 375 ms - 297 ms = 78 ms

The overhead of an UDF in CPU time = 7750 ms - 297 ms = 7453 ms

Here's the Sql code...

-- set up an auxiliary table Numbers with 128K rows:

CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY)
GO
DECLARE @i INT;
SET @i = 1;
INSERT INTO dbo.Numbers(n) SELECT 1;
WHILE @i<128000 BEGIN
  INSERT INTO dbo.Numbers(n)
    SELECT n + @i FROM dbo.Numbers;
  SET @i = @i * 2;
END;
GO

-- the tables

CREATE TABLE dbo.Animals
(AnimalId INT NOT NULL IDENTITY PRIMARY KEY,
AnimalType TINYINT NOT NULL, -- 1: Mammal, 2:Reptile, etc..
Name VARCHAR(30))
GO
ALTER TABLE dbo.Animals
ADD CONSTRAINT UNQ_Animals UNIQUE(AnimalId, AnimalType)
GO
CREATE FUNCTION dbo.GetAnimalType(@AnimalId INT)
RETURNS TINYINT
AS
BEGIN
DECLARE @ret TINYINT;
SELECT @ret = AnimalType FROM dbo.Animals
  WHERE AnimalId = @AnimalId;
RETURN @ret;
END
GO
CREATE TABLE dbo.Mammals
(AnimalId INT NOT NULL PRIMARY KEY,
SomeOtherStuff VARCHAR(10),
CONSTRAINT Chk_AnimalType_Mammal CHECK(dbo.GetAnimalType(AnimalId)=1)
);
GO

--- populating with UDF:

INSERT INTO dbo.Animals
  (AnimalType, Name)
SELECT 1, 'some name' FROM dbo.Numbers;
GO
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
INSERT INTO dbo.Mammals
(AnimalId,SomeOtherStuff)
SELECT n, 'some info' FROM dbo.Numbers;

results are:

SQL Server parse and compile time: 
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Mammals'. Scan count 0, logical reads 272135, 
    physical reads 0, read-ahead reads 0, lob logical reads 0, 
    lob physical reads 0, lob read-ahead reads 0.
Table 'Numbers'. Scan count 1, logical reads 441, physical reads 0, 
    read-ahead reads 0, lob logical reads 0, lob physical reads 0, 
    lob read-ahead reads 0.

SQL Server Execution Times:
    CPU time = 7750 ms,  elapsed time = 7830 ms.

(131072 row(s) affected)

--- populating with FK:

CREATE TABLE dbo.Mammals2
(AnimalId INT NOT NULL PRIMARY KEY,
AnimalType TINYINT NOT NULL,
SomeOtherStuff VARCHAR(10),
CONSTRAINT Chk_Mammals2_AnimalType_Mammal CHECK(AnimalType=1),
CONSTRAINT FK_Mammals_Animals FOREIGN KEY(AnimalId, AnimalType)
  REFERENCES dbo.Animals(AnimalId, AnimalType)
);

INSERT INTO dbo.Mammals2
(AnimalId,AnimalType,SomeOtherStuff)
SELECT n, 1, 'some info' FROM dbo.Numbers;

results are:

SQL Server parse and compile time: 
   CPU time = 93 ms, elapsed time = 100 ms.
Table 'Animals'. Scan count 1, logical reads 132, physical reads 0,
    read-ahead reads 0, lob logical reads 0, lob physical reads 0, 
    lob read-ahead reads 0.
Table 'Mammals2'. Scan count 0, logical reads 275381, physical reads 0,
   read-ahead reads 0, lob logical reads 0, lob physical reads 0, 
   lob read-ahead reads 0.
Table 'Numbers'. Scan count 1, logical reads 441, physical reads 0,
   read-ahead reads 0, lob logical reads 0, lob physical reads 0, 
   lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 375 ms,  elapsed time = 383 ms.

-- populating without any integrity:

CREATE TABLE dbo.Mammals3
(AnimalId INT NOT NULL PRIMARY KEY,
SomeOtherStuff VARCHAR(10)
);
INSERT INTO dbo.Mammals3
(AnimalId,SomeOtherStuff)
SELECT n,  'some info' FROM dbo.Numbers;

results are:
SQL Server parse and compile time: CPU time = 1 ms, elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 66 ms.
Table 'Mammals3'. Scan count 0, logical reads 272135, physical reads 0,
    read-ahead reads 0, lob logical reads 0, lob physical reads 0,
    lob read-ahead reads 0.
Table 'Numbers'. Scan count 1, logical reads 441, physical reads 0, 
    read-ahead reads 0, lob logical reads 0, lob physical reads 0, 
    lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 297 ms,  elapsed time = 303 ms.

(131072 row(s) affected)

The overhead of an FK in CPU time = 375 ms - 297 ms = 78 ms
The overhead of an UDF in CPU time = 7750 ms - 297 ms = 7453 ms

回答2:

Have a unique constraint on Animals(AnimalId, AnimalType) Add AnimalType to Mammals, and use a check constraint to make sure it is always 1. Have a FK refer to (AnimalId, AnimalType).

回答3:

You can create a CHECK CONSTRAINT on the column.

ALTER TABLE Mammals
ADD CONSTRAINT CHK_AnimalType CHECK (dbo.fnGetAnimalType(animalId) = 1 );

Now you need a function fnGetAnimalType that will return the animalType of the given animalId.

Here is more info from MSDN.

回答4:

To give a strong guarantee, you'll need two check constraints going both ways. If you only constrain Mammals someone could update Animals.AnimalType and get the data in an inconsistent state.

回答5:

I think you want to use a Check constraint within the Mammals table.

http://msdn.microsoft.com/en-us/library/ms188258.aspx

回答6:

This sounds like table inheritance - I recently asked a question (can't link because I'm too new here!) whose answer lead to finding this solution.

I'm working with monetary Transactions where a Transaction is the parent table and transaction types such as Cheque, Bank Transfer, and CC are the child tables. Using a lookup table for transaction types, foreign key constraints, and computed columns I was able to enforce the type constraint on each of the child tables.

Check out the article for implementation details.

回答7:

Here is an alternative approach that builds on the technique discussed in "Enforcing Complex Constraints with Indexed Views" to enforce the AnimalType constraint. The advantage of this approach is that it avoids the extra column in the subtype table; however, it is slower than the composite foreign key presented in AlexKuznetsov's answer (although nowhere near as abysmally slow as the UDF approach).

I ran the same series of tests as AlexKuznetsov, but increased the number of rows from 131,072 to 1,048,576. Here is a summary of the results:

-- No integrity check:                         2488 ms.
-- With composite foreign key:                 4404 ms.
-- With indexed view and unique index:         7063 ms. <- new
-- With check constraint calling scalar UDF:  78304 ms.

Schema definitions:

CREATE TABLE dbo.Mammals4
(
    AnimalId INT NOT NULL PRIMARY KEY
        REFERENCES dbo.Animals (AnimalId),
    SomeOtherStuff VARCHAR(10)
);

CREATE TABLE dbo.TwoRows   -- can be reused for other similar indexed views
(
    n TINYINT NOT NULL PRIMARY KEY
)

INSERT INTO dbo.TwoRows
VALUES (1), (2)

GO

CREATE VIEW dbo.CK_Mammals4 WITH SCHEMABINDING
AS
    SELECT M.AnimalId
    FROM dbo.Mammals4 M
        JOIN dbo.Animals A
            ON A.AnimalId = M.AnimalId
        CROSS JOIN dbo.TwoRows
    WHERE A.AnimalType != 1

GO

CREATE UNIQUE CLUSTERED INDEX CKIX_Mammals4
ON dbo.CK_Mammals4 (AnimalId)

Tests and results:

-- No integrity check:

INSERT INTO dbo.Mammals3 (AnimalId, SomeOtherStuff)
SELECT n, 'some info' FROM dbo.Numbers;

Table 'Mammals3'. Scan count 0, logical reads 2969171, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Numbers'. Scan count 1, logical reads 1724, physical reads 3, read-ahead reads 1720, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2247 ms,  elapsed time = 2488 ms.

-- With composite foreign key:

INSERT INTO dbo.Mammals2 (AnimalId, AnimalType, SomeOtherStuff)
SELECT n, 1, 'some info' FROM dbo.Numbers;

Table 'Animals'. Scan count 1, logical reads 1432, physical reads 3, read-ahead reads 1428, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Mammals2'. Scan count 0, logical reads 3163291, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Numbers'. Scan count 1, logical reads 1724, physical reads 3, read-ahead reads 1720, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 3822 ms,  elapsed time = 4404 ms.

-- With indexed view and unique index:

INSERT INTO dbo.Mammals4 (AnimalId, SomeOtherStuff)
SELECT n, 'some info' FROM dbo.Numbers

Table 'CK_Mammals4'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Animals'. Scan count 2, logical reads 4953, physical reads 6, read-ahead reads 4945, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 2978842, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Mammals4'. Scan count 0, logical reads 3162616, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Numbers'. Scan count 1, logical reads 1724, physical reads 3, read-ahead reads 1720, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 6209 ms,  elapsed time = 7063 ms.

-- With check constraint calling scalar UDF:

INSERT INTO dbo.Mammals (AnimalId, SomeOtherStuff)
SELECT n, 'some info' FROM dbo.Numbers;

Table 'Mammals'. Scan count 0, logical reads 2969171, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Numbers'. Scan count 1, logical reads 1724, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 77424 ms,  elapsed time = 78304 ms.