是否可以向外键添加逻辑约束？ [英] Is it possible to add a logic Constraint to a Foreign Key?
问题描述
我有两个表，我添加了一个外键约束。 Kewl - 工作伟大。现在，是否可以进一步约束父表中的某些数据的关系？
基本上，我在父表中有动物，而对于子表只有包含父数据为... um ..哺乳动物的数据。
例如
动物
^^^^^^^
AnimalId INT PK NOT NULL IDENTITY
AnimalType TINYINT NOT NULL - 1：Mammal，2：Reptile等。
名称
哺乳动物
^^^^^^^
AnimalId INT PK FK NOT NULL
NumberOfMammaryGlads TINYINT NOT NULL
所以，我想确保AnimalId只能是类型Animals.AnimalType = 1
这是可能的吗？
我不想允许有人试图在爬行动物中插入一些信息，在子表中。
干杯：）
编辑：
我以为我不得不使用检查约束（以下确认我的前两个答案 - 欢呼！），但我不知道如何（例如。
更新：
Alex有一个非常好的帖子
我运行了一个小标准 - 在这个
CPU时间中FK的开销= 375 ms - 297 ms = 78 ms
CPU时间中UDF的开销= 7750毫秒 - 297毫秒= 7453毫秒
这里是Sql代码...
- 设置辅助表128K行的数字：
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
- 表格
CREATE TABLE dbo.Animals
（AnimalId INT NOT NULL IDENTITY PRIMARY KEY，
AnimalType TINYINT NOT NULL， - 1：Mammal，2：Reptile等..
Name VARCHAR（30））
GO
ALTER TABLE dbo.Animals
ADD CONSTRAINT UNQ_Animals UNIQUE（AnimalId，AnimalType）
GO
创建函数dbo.GetAnimalType（@AnimalId INT）
返回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 .GetAnimalType（AnimalId）= 1）
）;
GO
---填充UDF：
INSERT INTO dbo.Animals
（AnimalType，Name）
SELECT 1，'some name'FROM dbo.Numbers;
GO
SET STATISTICS IO ON
设置统计时间
GO
插入到dbo.Mammals
（AnimalId，SomeOtherStuff）
SELECT n ，'some info'from dbo.Numbers;
结果是：
SQL Server解析和编译时间：
CPU时间= 0 ms，已用时间= 2 ms。
表'哺乳动物'。扫描计数0，逻辑读数272135，
物理读取0，预读读取0，lob逻辑读取0，
lob物理读取0，lob预读读取0.
表'Numbers '。扫描计数1，逻辑读数441，物理读取0，
预读读取0，lob逻辑读取0，lob物理读取0，
lob预读读取0.
SQL Server执行时间：
CPU时间= 7750毫秒，已用时间= 7830毫秒。
（受影响的131072行）
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）
参考dbo.Animals ）
）;
INSERT INTO dbo.Mammals2
（AnimalId，AnimalType，SomeOtherStuff）
SELECT n，1，'some info'from dbo.Numbers;
结果是：
SQL Server解析和编译时间：
CPU时间= 93 ms，已用时间= 100 ms。
表'动物'。扫描计数1，逻辑读取132，物理读取0，
预读读取0，lob逻辑读取0，lob物理读取0，
lob预读为0.
表'Mammals2 '。扫描计数0，逻辑读取275381，物理读取0，
预读读取0，lob逻辑读取0，lob物理读取0，
lob预读读取0.
表'Numbers '。扫描计数1，逻辑读数441，物理读取0，
预读读取0，lob逻辑读取0，lob物理读取0，
lob预读读取0.
SQL Server执行时间：
CPU时间= 375 ms，已用时间= 383 ms。
- 完整填充：
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;
结果是：
SQL Server解析和编译时间：
CPU时间= 1 ms，已用时间= 1 ms。
SQL Server执行时间：
CPU时间= ，经过时间= 66ms。
Table'Mammals3'。扫描计数0，逻辑读数272135，物理读取0，
预读读取0，lob逻辑读取0，lob物理读取0，
lob预读读取0.
表'Numbers '。扫描计数1，逻辑读数441，物理读取0，
预读读取0，lob逻辑读取0，lob物理读取0，
lob预读读取0.
SQL Server执行时间：
CPU时间= 297 ms，已用时间= 303 ms。
（受影响的131072行）
FK in CPU时间= 375 ms - 297 ms = 78 ms
CPU时间中UDF的开销= 7750 ms - 297 ms = 7453 ms
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!
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
这篇关于是否可以向外键添加逻辑约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
