如果你总是有一个状态要检查，这可以通过 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 这里