添加检查单独(链接)表的值的约束

本文介绍了添加检查单独(链接)表的值的约束的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有两张桌子:

Book(BookID, Title, Author, Decision)

BookShipment(BookID, ShipmentID)

CREATE TABLE BookShipment(
BookID CHAR(4),
ShipmentID(7)
CONSTRAINT pk_BookShipment PRIMARY KEY (BookID, ShipmentID),
CONSTRAINT fk_BookShipment_Book FOREIGN KEY (BookID) REFERENCES Book(BookID));

这个想法是一本书需要批准"；在将其添加到货件之前.如果它被拒绝"它不会被添加.

The idea is that a Book needs to be "Approved" before it's added to a Shipment. If it's "Rejected" it won't be added.

有没有办法向 BookShipment 添加额外的约束，当添加新的 BookID 时，会检查 Decision 下的 DecisionBook 表等于 Approved(对于那个 BookID)?

Is there a way to add an additional constraint to BookShipment that, when a new BookID is added, would check that Decision under the Book table is equal to Approved (for that BookID)?

推荐答案

如果你总是有一个状态要检查，这可以通过 FK 约束的一些小技巧来完成:

If you'll always have a single status to check, this can be done with little tricks on FK constraint:

在 Books(BookId, Decision) 上创建虚拟唯一索引.
将计算列添加到 BookShipment，值为 Approved.
在 FK 约束中引用创建的唯一索引.

Create dummy unuque index on Books(BookId, Decision).
Add calculated column to BookShipment with value Approved.
Reference the created unique index in FK constraint.

在CHECK约束中定义UDF应该是更灵活的方式.

Defining UDF in CHECK constraint should be more flexible way for this.

create table book (
  BookID int identity(1,1) primary key,
  Title varchar(100),
  Author varchar(100),
  Decision varchar(100),

  --Dummy constraint for FK
  constraint u_book unique(bookid, decision)
);

CREATE TABLE BookShipment(
  BookID int,
  ShipmentID varchar(7),
  --Dummy column for FK
  approved as cast('Approved' as varchar(100)) persisted

  CONSTRAINT pk_BookShipment PRIMARY KEY (BookID),
  CONSTRAINT fk_BookShipment_Book_Approved
    FOREIGN KEY (BookID, approved)
    REFERENCES Book(BookID, decision)
);

insert into book (Title, Author, Decision)
select 'A', 'B', 'Approved' union all
select 'A', 'B', 'New'
;

--2 rows affected

insert into BookShipment values(1, 1);

--1 rows affected

insert into BookShipment values(2, 2);

/*

insert into BookShipment values(2, 2);


Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_BookShipment_Book_Approved". The conflict occurred in database "fiddle_ea408f09b06247a78b47ea9c353eda10", table "dbo.book".
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.
*/

db<>fiddle 这里

这篇关于添加检查单独(链接)表的值的约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

BookId