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

2021-12-26 00:00:00 sql sql-server database-design

我有两张桌子:

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 这里

相关文章