CODEWITHSUNDEEP
mysqlsqldatabaseforeign-keys
Anuj Garg
Anuj Garg

Reputation: 582

Creation of a database

CREATE TABLE EMPLOYEE
( Fname VARCHAR(15) NOT NULL,
Minit CHAR,
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR(30),
Sex CHAR,
Salary DECIMAL(10,2),
Super_ssn CHAR(9),
Dno INT NOT NULL,
PRIMARY KEY (Ssn),
FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber) );

CREATE TABLE DEPARTMENT
( Dname VARCHAR(15) NOT NULL,
Dnumber INT NOT NULL,
Mgr_ssn CHAR(9) NOT NULL,
Mgr_start_date DATE,
PRIMARY KEY (Dnumber),
UNIQUE (Dname),
FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn) );

CREATE TABLE DEPT_LOCATIONS
( Dnumber INT NOT NULL,
Dlocation VARCHAR(15) NOT NULL,
PRIMARY KEY (Dnumber, Dlocation),
FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber) );

CREATE TABLE PROJECT
( Pname VARCHAR(15) NOT NULL,
Pnumber INT NOT NULL,
Plocation VARCHAR(15),
Dnum INT NOT NULL,
PRIMARY KEY (Pnumber),
UNIQUE (Pname),
FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber) );

CREATE TABLE WORKS_ON
( Essn CHAR(9) NOT NULL,
Pno INT NOT NULL,
Hours DECIMAL(3,1) NOT NULL,
PRIMARY KEY (Essn, Pno),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber) );

CREATE TABLE DEPENDENT
( Essn CHAR(9) NOT NULL,
Dependent_name VARCHAR(15) NOT NULL,
Sex CHAR,
Bdate DATE,
Relationship VARCHAR(8),
PRIMARY KEY (Essn, Dependent_name),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn) );

Here I am not able to create any table because each table is referencing to some other table using foreign key constraint.So kindly help me how to create the whole database.for eg. if I am creating EMPLOYEE table it is searching for DEPARTMENT table and not allowing to create the table and I trying to create DEPARTMENT then it is searching for EMPLOYEE table again not allowing the creation of the table.

Upvotes: 0

Views: 308

Answers (3)

Rahul
Rahul

Reputation: 77866

One simple solution is remove the FK to DEPARTMENT (the below one) from creation of EMPLOYEE table and create all your tables/schema. 

FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)

After then, recreate this FK on EMPLOYEE table saying

ALTER TABLE EMPLOYEE ADD CONSTRAINT fk_emp 
FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber);

See a successful Table creation Demo http://sqlfiddle.com/#!2/410ce

Upvotes: 0

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120917

Just add the foreign keys separately when the tables have been created, e.g.

ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMPLOYEE_DEPARTMENT 
     FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber);

Upvotes: 1

Iulian
Iulian

Reputation: 169

you can try to create and populate the tables without foreign key constraints and after that add the foreign key constraints.

Upvotes: 1

Related Questions