sql server语言 - 多表约束.UDF

Question

我有两个表:表A和表b。这两个表用A中的主键和b中的外键连接。

表答:

CREATE TABLE [BIO].[table_A](
    [table_A_id] [int] IDENTITY(1,1) NOT NULL,  
    [type_id] [nvarchar](2) NOT NULL    
CONSTRAINT [PK_table_A] PRIMARY KEY CLUSTERED 
(
    [table_A_id] ASC
))

表B:

CREATE TABLE [BIO].[table_B](
    [table_B_id] [int] IDENTITY(1,1) NOT NULL,
    [table_A_id] [int] NOT NULL,
    [analysis_id] [tinyint] NOT NULL
CONSTRAINT [PK_table_B] PRIMARY KEY CLUSTERED 
(
    [table_B_id] ASC
))
ALTER TABLE [BIO].[table_B] WITH CHECK 
ADD CONSTRAINT [FK_table_B_table_A] FOREIGN KEY([table_A_id])
REFERENCES [BIO].[table_A] ([table_A_id])
GO
ALTER TABLE [BIO].[table_B] CHECK CONSTRAINT [FK_table_B_table_A]
GO

表B必须根据表A中的值只包含特定的值。例如，如果我在表A中有BL，那么我只能在表B中有1或3个;如果表A中有ST，那么表b中就只能有2或4。

我设置了一个桥表，它定义了这些组合:BL→1或3,ST→2或4。

桥表:

CREATE TABLE [QRY].[bridge_table](
    [type_id] [nvarchar](2) NOT NULL,
    [analysis_id] [tinyint] NOT NULL,
CONSTRAINT [PK_bridge_table] PRIMARY KEY CLUSTERED 
(
    [type_id] ASC,
    [analysis_id] ASC
))

我目前使用一个约束每个表，以确保任何插入或更新是正确的根据桥表中定义的组合。这两个约束是基于udf的。

表B上的约束:

ALTER TABLE [BIO].[Table_B] WITH CHECK 
ADD CONSTRAINT [CK_chkAnalysisType] 
CHECK (([QRY].[TypeAnalysisMatch_table_B]([table_A_id])>(0)))
GO

UDF:

CREATE FUNCTION [QRY].[TypeAnalysisMatch_table_B] (@table_A_id int)
RETURNS int
AS
BEGIN
    RETURN
    (
    SELECT
        Count(BIO.table_A.table_A_id) AS cnt_rec
    FROM
        QRY.bridge_table
        INNER JOIN BIO.table_A ON QRY.bridge_table.type_id = BIO.table_A.type_id
        INNER JOIN BIO.table_B ON 
                QRY.bridge_table.analysis_id = BIO.table_B.analysis_id
            AND BIO.table_A.table_A_id = BIO.table_B.table_A_id
    WHERE
        BIO.table_A.table_A_id = @table_A_id
    )
END

它对INSERT工作良好，但对UPDATE不一致。此外，当我读到应该避免约束中的udf时，我正在寻找更好的解决方案。

这些约束的有效替代方案是什么?

Answer 1

你是对的，在CHECK约束中使用UDF可能会很棘手，一些UPDATE语句可能会绕过检查:

http://sqlblog.com/blogs/tibor_karaszi/archive/2009/12/17/be-careful-with-constraints-calling-udfs.aspx

MSSQL: Update语句避免CHECK约束

正如您在SO问题中看到的那样，答案建议使用触发器进行检查。编写一个正确有效的触发器也不是一件容易的事。

---

我假设你的bridge_table包含以下数据:

type_id    analysis_id
BL         1
BL         3
ST         2
ST         4

我将只使用外键设置这些约束，而不使用UDF。但是，它需要一些(最小的)数据副本。我假设真正的table_A和table_B比这个例子有更多的列。

1。在table_A中包含type_id的主键:

CREATE TABLE [table_A](
    [table_A_id] [int] IDENTITY(1,1) NOT NULL,
    [type_id] [nvarchar](2) NOT NULL,
CONSTRAINT [PK_table_A] PRIMARY KEY CLUSTERED
(
    [table_A_id] ASC,
    [type_id] ASC
))

2。为table_B添加type_id列。是的，与您在table_A中已经拥有的列相同。这就是我上面提到的重复数据:

CREATE TABLE [table_B](
    [table_B_id] [int] IDENTITY(1,1) NOT NULL,
    [table_A_id] [int] NOT NULL,
    [type_id] [nvarchar](2) NOT NULL,
    [analysis_id] [tinyint] NOT NULL
CONSTRAINT [PK_table_B] PRIMARY KEY CLUSTERED 
(
    [table_B_id] ASC
))

3。创建table_B和table_A在两列上的外键(table_A_id, type_id):

ALTER TABLE [table_B] WITH CHECK 
ADD CONSTRAINT [FK_table_B_table_A] FOREIGN KEY([table_A_id], [type_id])
REFERENCES [table_A] ([table_A_id], [type_id])

此约束保证table_B中重复的type_id值与table_A中的原始值一致。

4。为table_B和bridge_table创建两个列的外键(type_id, analysis_id):

ALTER TABLE [table_B] WITH CHECK 
ADD CONSTRAINT [FK_table_B_bridge_table] FOREIGN KEY([type_id], [analysis_id])
REFERENCES [bridge_table] ([type_id], [analysis_id])

5。现在您可以测试一切是否正常。

添加table_A:

INSERT INTO [table_A] ([type_id]) 
VALUES ('BL'), ('BL'), ('ST'), ('ZZ');
table_A_id    type_id
1             BL
2             BL
3             ST
4             ZZ

尝试插入有效数据到table_B:

INSERT INTO [dbo].[table_B] ([table_A_id],[type_id],[analysis_id])
VALUES (1,'BL',1)
INSERT INTO [dbo].[table_B] ([table_A_id],[type_id],[analysis_id])
VALUES (1,'BL',3)
INSERT INTO [dbo].[table_B] ([table_A_id],[type_id],[analysis_id])
VALUES (2,'BL',3)
INSERT INTO [dbo].[table_B] ([table_A_id],[type_id],[analysis_id])
VALUES (3,'ST',2)
INSERT INTO [dbo].[table_B] ([table_A_id],[type_id],[analysis_id])
VALUES (3,'ST',2)
table_B_id    table_A_id    type_id    analysis_id
1             1             BL         1
2             1             BL         3
3             2             BL         3
4             3             ST         2
5             3             ST         2

尝试插入无效数据:

INSERT INTO [dbo].[table_B] ([table_A_id],[type_id],[analysis_id])
VALUES (3,'ST',1)
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_table_B_bridge_table". 
The conflict occurred in database "tempdb", table "dbo.bridge_table".
The statement has been terminated.

这意味着ST不能有analysis_id=1

INSERT INTO [dbo].[table_B] ([table_A_id],[type_id],[analysis_id])
VALUES (3,'BL',1)
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_table_B_table_A". 
The conflict occurred in database "tempdb", table "dbo.table_A".
The statement has been terminated.

这意味着table_A中有table_A_id=3的行在type_id中没有BL。

外键也会继续检查所有UPDATE语句的数据一致性:

UPDATE [dbo].[table_B]
SET [type_id] = 'ST'
WHERE [table_B_id] = 1
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_table_B_table_A". 
The conflict occurred in database "tempdb", table "dbo.table_A".
The statement has been terminated.
UPDATE [dbo].[table_B]
SET [analysis_id] = 2
WHERE [table_B_id] = 1
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_table_B_bridge_table". 
The conflict occurred in database "tempdb", table "dbo.bridge_table".
The statement has been terminated.

但是这个可以:

UPDATE [dbo].[table_B]
SET [analysis_id] = 3
WHERE [table_B_id] = 1
(1 row(s) affected)