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.
- you can use check constraint, add this to table definition:
CONSTRAINT check_work_ids_are_not_equal CHECK work_1 <> work_2
- yes, you can create PK on (work_1, work_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 🙂