Creating tables with circular reference

Question

How can I create multiple tables with circular references. I have this data that I'd like to create all in one shot:

DROP TABLE employee CASCADE CONSTRAINTS;
CREATE TABLE employee (
  fname    varchar2(15) not null, 
  minit    varchar2(1),
  lname    varchar2(15) not null,
  ssn      char(9),
  bdate    date,
  address  varchar2(30),
  sex      char,
  salary   number(10,2),
  superssn char(9),
  dno      number(4),
  primary key (ssn),
  foreign key (superssn) references employee(ssn),
  foreign key (dno) references department(dnumber)
);

DROP TABLE department CASCADE CONSTRAINTS;
CREATE TABLE department (
  dname        varchar2(15) not null,
  dnumber      number(4),
  mgrssn       char(9) not null, 
  mgrstartdate date,
  primary key (dnumber),
  unique (dname),
  foreign key (mgrssn) references employee(ssn)
);

DROP TABLE dept_locations CASCADE CONSTRAINTS;
CREATE TABLE dept_locations (
  dnumber   number(4),
  dlocation varchar2(15), 
  primary key (dnumber,dlocation),
  foreign key (dnumber) references department(dnumber)
);

DROP TABLE project CASCADE CONSTRAINTS;
CREATE TABLE project (
  pname      varchar2(15) not null,
  pnumber    number(4),
  plocation  varchar2(15),
  dnum       number(4) not null,
  primary key (pnumber),
  unique (pname),
  foreign key (dnum) references department(dnumber)
);

DROP TABLE works_on CASCADE CONSTRAINTS;
CREATE TABLE works_on (
  essn   char(9),
  pno    number(4),
  hours  number(4,1),
  primary key (essn,pno),
  foreign key (essn) references employee(ssn),
  foreign key (pno) references project(pnumber)
);

DROP TABLE dependent CASCADE CONSTRAINTS;
CREATE TABLE dependent (
  essn           char(9),
  dependent_name varchar2(15),
  sex            char,
  bdate          date,
  relationship   varchar2(8),
  primary key (essn,dependent_name),
  foreign key (essn) references employee(ssn)
);

Please help!!

Any reason thy you can't run this? Are you getting an error code? — David Starkey, Sep 16 '13 at 16:16
Not sure who voted to close the question without leaving a comment, but I think it's a good question. You might want to re-think the not null constraint on department.mgrssn. If a manager leaves, is he intstantly replaced 100% of the time? If not, your data model does not support the business requirement. — Dan Bracuk, Sep 16 '13 at 16:19
@DavidStarkey, the code won't run because the employee and department tables reference each other. — Dan Bracuk, Sep 16 '13 at 16:20
@DAn, ah thanks for pointing that out. Perhaps we should simplify this question to focus on that issue. — David Starkey, Sep 16 '13 at 16:38
possible duplicate of [Is there any other way to create constraints during SQL table creation?](http://stackoverflow.com/questions/9090431/is-there-any-other-way-to-create-constraints-during-sql-table-creation) — Jon Heller, Sep 16 '13 at 18:13

tbone · Accepted Answer · 2013-09-16T18:28:00.170

8

You can create the tables and then create the constraints:

CREATE TABLE employee (
  fname    varchar2(15) not null, 
  minit    varchar2(1),
  lname    varchar2(15) not null,
  ssn      char(9),
  bdate    date,
  address  varchar2(30),
  sex      char,
  salary   number(10,2),
  superssn char(9),
  dno      number(4),
  primary key (ssn)
);

CREATE TABLE department (
  dname        varchar2(15) not null,
  dnumber      number(4),
  mgrssn       char(9) not null, 
  mgrstartdate date,
  primary key (dnumber),
  unique (dname)
);

ALTER TABLE employee ADD CONSTRAINT fk_dno FOREIGN KEY (dno) REFERENCES department(dnumber) DEFERRABLE;
ALTER TABLE department ADD CONSTRAINT fk_mgrssn FOREIGN KEY (mgrssn) REFERENCES employee(ssn) DEFERRABLE;

When inserting, you can set the constraints deferred, meaning that Oracle won't verify them until you commit.

ALTER SESSION SET CONSTRAINTS = DEFERRED;
insert into employee (fname, lname, ssn, dno) values ('Joe','Blow','123456789', 10);
insert into department(dname, dnumber, mgrssn) values ('Dept1',10, '123456789');
commit;

You can also set them up initially deferred when creating them, but the constraints must be "deferrable" at the least to do the above.

edited Sep 16 '13 at 18:28

answered Sep 16 '13 at 17:13

tbone

15,107
3
33
40

Make sure you have at least one record in each table before you add the constraints. Otherwise you have a different version of the same problem. – Dan Bracuk Sep 16 '13 at 17:57
3

@DanBracuk not a problem if you set them initially deferred, see my additions to the answer. – tbone Sep 16 '13 at 18:23
Can you confirm this answer? I'll give you the chk if you post an answer. http://dba.stackexchange.com/a/58959/32234 – Feb 14 '14 at 17:58
@Gracchus Hi, your question relates to behavior of cascade deletes (and appears to be for PostgreSQL). This question is related to using deferred contraints to accomplish inserts. Have you tried to setup a few test tables with the required cascade deletes and tried deleting? I dont have access to PostgreSQL atm. – tbone Feb 14 '14 at 19:22
@tbone Thank you for looking tbone! I was hoping that could be a last resort after I create the structure. I'd rather keep plodding on my design than test if an answer can be achieved, so I guess for now, I'll wait for an answer. – Feb 14 '14 at 19:25

Creating tables with circular reference

1 Answers1