Is it possible to add a logic Constraint to a Fore

Question

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!

Answer 1

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.

Answer 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).

Answer 3

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

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

Answer 4

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.

Answer 5

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.

Answer 6

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.