How do I make a citation table where work can't refer to itself?

All we need is an easy explanation of the problem, so here it is.

CREATE TABLE Work(id INT NOT NULL AUTO_INCREMENT,
    title CHAR(20),
    date DATETIME,
    description VARCHAR(1000),
    CONSTRAINT Work_PK PRIMARY KEY (id),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- In the following, do we have to worry about work_1 being = to work_2, how can we prevent this ? 

CREATE TABLE Work_Citation(id INT NOT NULL AUTO_INCREMENT,
                       work_1 INT NOT NULL,
                       work_2 INT NOT NULL,
                       CONSTRAINT Work_Citation_PK_1 PRIMARY KEY (id),
                       CONSTRAINT Work_Citation_PK_2 UNIQUE (work_1,work_2),
                       CONSTRAINT Work_Citation_FK_1 FOREIGN KEY (work_1) REFERENCES Work(id),
                       CONSTRAINT Work_Citation_FK_2 FOREIGN KEY (work_2) REFERENCES Work(id)) ENGINE=InnoDB DEFAULT  CHARSET=utf8;

-- A second query, can I do without the id column in Work_Citation? Any comments on when we should / should not use it?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

  1. you can use check constraint, add this to table definition:
CONSTRAINT check_work_ids_are_not_equal CHECK work_1 <> work_2
  1. yes, you can create PK on (work_1, work_2)
  2. it has a sense to add index on Work_citation(work_2). This way you will not need to scan the whole Work_Citation table when delete rows from Work table to check that the are no referencing rows

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply