2 Replies - 1417 Views - Last Post: 29 November 2006 - 07:16 AM Rate Topic: -----

#1 thef0rce  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 39
  • Joined: 02-November 06

quick database question

Posted 24 November 2006 - 10:05 AM

I am making a simple database to store information about research papers. Each paper has a title, is written by one or more authors, and is published in only one journal.

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


Thanks :)

Is This A Good Question/Topic? 0
  • +

Replies To: quick database question

#2 thef0rce  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 39
  • Joined: 02-November 06

Re: quick database question

Posted 25 November 2006 - 06:00 AM

I got it

ON DELETE CASCADE for all my keys :D

yay
Was This Post Helpful? 0
  • +
  • -

#3 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: quick database question

Posted 29 November 2006 - 07:16 AM

Be very carefull with that one...
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1