These are my tables. Publication is used to resolve the many to many relationship between author and paper. Each author can write many papers, each paper can have many authors.
CREATE TABLE Journal ( jID int not null Primary Key, name VARCHAR(50), year int ); CREATE TABLE Author ( aID int not null Primary Key, first_initial CHAR(1), last_name VARCHAR(20), address VARCHAR(20) ); CREATE TABLE PAPER ( pID int not null Primary Key, jID int not null, title VARCHAR(30) ); CREATE TABLE Publication ( aID int not null, pID int not null, CONSTRAINT pk_publication PRIMARY KEY (aID, pID) );
I'm thinking that I should make aID and pID from Publication foreign keys to author and paper respectively.
My question is, would I find it difficult to delete one or more papers from the paper table if I set these foreign keys? If not, how would I go about doing so without violating the foreign key constraints?
so what I'm thinking about doing is:
CREATE TABLE Publication ( aID int not null, pID int not null, CONSTRAINT pk_publication PRIMARY KEY (aID, pID), CONSTRAINT fk_pub FOREIGN KEY (aID) REFERENCES Author (aID), CONSTRAINT fk_publication FOREIGN KEY (pID) REFERENCES Paper (pID) );