Re: How to insert values?

From: andrewst <member14183_at_dbforums.com>
Date: Mon, 19 May 2003 12:46:08 +0000
Message-ID: <2896540.1053348368@dbforums.com>

Originally posted by Me
> On Sun, 18 May 2003 15:04:21 -0700, Ana C. Dent wrote:
>
> :No amount of hand waving makes a poor design "elegant".
>
> But this design is given in my text book and he uses it constantly to
> explain the rest of the queries in the book.
> He has given ER diagrams theory etc. all based on this example itself.
>
> Is the whole thing wrong?
>
> And how does one create such tables?
>
> Thanks.
> --
> This is my last post in this thread.
> Thanks everyone, for your help.

I am surprised that no one has yet given you the answer to this question. Oracle makes it perfectly possible to (a) create tables with circular dependencies in one step, and (b) to enter data into tables with circular dependencies:

CREATE SCHEMA AUTHORIZATION myschema
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),

SUPERSSN CHAR(9),
DNO INT NOT NULL,
PRIMARY KEY(SSN),

FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNUMBER) DEFERRABLE INITIALLY DEFERRED
)
CREATE TABLE DEPARTMENT
(
DNAME VARCHAR(15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL,
MGRSTARTDATE DATE,
PRIMARY KEY(DNUMBER),
UNIQUE(DNAME),
FOREIGN KEY(MGRSSN) REFERENCES EMPLOYEE(SSN)) /

(I have corrected various typos and omitted other tables that are irrelevant to the problem).

The above CREATE SCHEMA statement has created EMPLOYEE and DEPARTMENT with circular dependencies. The "DEFERRABLE INITIALLY DEFERRED" on EMPLOYEE.DNO foreign key allows you to create an employee before the referenced department exists, like this:

SQL> insert into employee(fname,lname,ssn,dno) values SQL> ('Fred','Smith','123',10);

1 row created.

SQL> insert into department(dname,dnumber,mgrssn) values SQL> ('HR',10,'123'); 1 row created.

SQL> commit;

Commit complete.

Of course, the "DEFERRABLE INITIALLY DEFERRED" could have been used on the DEPARTMENT.MGRSSN foreign key as well (or instead), allowing you to insert the department first.

This isn't "bad design" as other have deemed it, it is perfectly logical (provided your application allows you to create employees and departments within a single transaction). It is perfectly valid to have the business rules "every department must have a manager" and "every employee must be in a department"; it is just that prior to CREATE SCHEMA and DEFERRABLE, it wasn't technically possible to enforce both rules via constraints.

--
Posted via http://dbforums.com

Received on Mon May 19 2003 - 07:46:08 CDT