添加约束来检查单独的(链接的)表中的值 [英] Adding constraints that check a separate (linked) table for a value

问题描述

我有两个表:

书(书号，书名，作者，决定)

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 Book 表是否等于 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<>小提琴此处

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