SQL statement to limit an attribute

Question

I have 3 tables REVIEW,PAPER and PCMEMBER and the codes for it is as mentioned below:

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')),
);

I'm trying to add a condition where "Each paper will be reviewed by exactly 3 PC members". Not sure what CHECK constraints I should be using? I just need this for creating tables. Thanks

Mike Dinescu · Answer 1 · 2016-03-27T06:40:03.360

1

One way you could implement this type of check constraint is with a scalar function that performs the check.

The following function, given a paper id, will check whether the paper is reviewed by 3 members only and it returns 1 if the condition is satisfied and 0 otherwise.

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

And then use this function in your check constraint like so:

CHECK (isReviewdByThreeMembers(paper_id) = 1)

You mentioned you want each paper to "only be reviewed by 3 members" but this condition can never be satisfied by records that don't already exist because each new paper would initially start with no reviews and incrementally get new reviews until it gets to 3.

If you were to modify the constraint to allow each paper to be reviewed by "up to 3 reviewers" then the constraint would actually be a bit more useful. Perhaps in conjunction with another constraint that would maybe prevent the status of a paper from transitioning into "REVIEWED" or "COMPLETED" once the 3 required reviews are completed.

edited Mar 27 '16 at 06:40

answered Mar 27 '16 at 06:33

Mike Dinescu

54,171
16
118
151

Thank you for your input Mike. Yes, you're right. Each paper has to have 3 reviewers. Can this be included inside the create table statement? – Arvind Suryanarayana Mar 27 '16 at 06:41
Yes, it can, as long as you create the function first - otherwise, you can use ALTER TABLE ADD CONSTRAINT – Mike Dinescu Mar 27 '16 at 06:42
ALTER TABLE seems like an option. Can you help me with ALTER TABLE code? – Arvind Suryanarayana Mar 27 '16 at 06:46
I just re-read your question and realized that you're working with MySQL - which unfortunately doesn't support CHECK constraints (http://dev.mysql.com/doc/refman/5.7/en/create-table.html). Some people suggest you could use a trigger to work-around the lack of enforced CHECK constraints (http://stackoverflow.com/questions/7522026/how-do-i-add-a-check-constratint-to-a-table) Sorry – Mike Dinescu Mar 27 '16 at 06:55
Ya I was kinda looking out for triggers but not sure what I need to use. Thank you for your help. – Arvind Suryanarayana Mar 27 '16 at 07:02

SQL statement to limit an attribute

1 Answers1