mysql - 限制属性的SQL语句

Question

我有3个表REVIEW、PAPER和PCMEMBER，其代码如下:

CREATE TABLE REVIEW(
due_date DATE NOT NULL,
review_date DATE NOT NULL,
recommendation VARCHAR(50) NOT NULL,
comment VARCHAR(50) NOT NULL,
pcmem_id NUMBER(10) NOT NULL,
paper_id NUMBER(10) NOT NULL,
CONSTRAINT review_pk PRIMARY KEY (pcmem_id,paper_id),
CONSTRAINT review_fk FOREIGN KEY(paper_id)
REFERENCES PAPER(paper_id),
CONSTRAINT review_fk1 FOREIGN KEY(pcmem_id)
REFERENCES PCMEMBER(pcmem_id));

CREATE TABLE PCMEMBER(
pcmem_id NUMBER(10) NOT NULL PRIMARY KEY,
pc_fname VARCHAR(20) NOT NULL,
pc_sname VARCHAR(20) NOT NULL,
pc_title VARCHAR (20) NOT NULL,
pc_position VARCHAR(20) NOT NULL,
affiliation VARCHAR(20) NOT NULL,
pc_email VARCHAR(20) NOT NULL,
track_id NUMBER(6) NOT NULL,
CONSTRAINT pcmember_fk FOREIGN KEY(track_id)
REFERENCES TRACK(track_id));

CREATE TABLE PAPER(
paper_id NUMBER(10) PRIMARY KEY NOT NULL,
paper_title VARCHAR(20) NOT NULL,
abstract VARCHAR(50) NOT NULL,
paper_type VARCHAR(20) NOT NULL,
submission_date DATE NOT NULL,
track_id NUMBER(6) NOT NULL,
CONSTRAINT paper_fk FOREIGN KEY(track_id)
REFERENCES TRACK(track_id),
CONSTRAINT chk_type CHECK(paper_type IN ('full paper','Research-in-Progress','posters')),
);

我正在尝试添加一个条件，即“每篇论文将由恰好 3 位 PC 成员审阅”。不确定我应该使用什么 CHECK 约束？我只需要这个来创建表格。谢谢

Answer 1

实现此类检查约束的一种方法是使用执行检查的标量函数。

以下函数，给定论文 ID，将检查论文是否仅由 3 个成员审阅，如果满足条件，则返回 1，否则返回 0。

CREATE FUNCTION isReviewdByThreeMembers (
    @paperID NUMBER(10)
)
RETURNS INTEGER
AS
BEGIN
    DECLARE @count INTEGER

     SELECT @count = COUNT(*) 
       FROM Review 
      WHERE paper_id = @paperID


     RETURN CASE 
        WHEN @count = 3 THEN 1
        ELSE 0 
        END
END

然后在检查约束中使用此函数，如下所示:

CHECK (isReviewdByThreeMembers(paper_id) = 1)

您提到您希望每篇论文“仅由 3 名成员审阅”，但尚不存在的记录永远无法满足此条件，因为每篇新论文最初都会没有审阅，并逐渐获得新审阅，直到出现达到 3。

如果您要修改限制以允许每篇论文由“最多 3 名审阅者”进行审阅，那么该限制实际上会更有用。也许与另一个约束相结合，一旦 3 项所需的审阅完成，论文的状态就可能无法转变为“已审阅”或“已完成”。