Re: How to insert values?

Me wrote:
> On Sun, 18 May 2003 20:45:18 +0200, Frank wrote:
>
> :Me wrote:
> :> 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));
> :>
> :> CREATE TABLE DEPARTMENT
> :> (
> :> DNAME VARCHAR(15) NOT NULL,
> :> DNUMBER INT NOT NULL,
> :> MGRSSN CHAR(9) NOT NULLL,
> :> MGRSTARTDATE DATE,
> :> PRIMARY KEY(DNUMBER),
> :> UNIQUE(DNAME),
> :> FOREIGN KEY(MBRSSN) 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));
> :>
> :> What is the correct sequence of commands to create the above table?
> :>
> :> Any way I have managed to create this table but whenever I try to
> :> insert values I get the following similar error for all the tables:
> :>
> :> ORA-02291: integrity constraint (user.SYS_C00848) violated - parent
> :> key not found
> :>
> :> Thanks.
> :>
> :
> :Cannot be done, unless you disable the foreign key constraints.
> :I would reconsider this design; there's no clear starting point.
> :And char and varchar are usually
> :coded as varchar2 in Oracle, to name another issue.
>
> I don't know. I have a book
> Fundamentals of Database Systems
> Ramez Elmasri
> Shamkant B. Navthe
>
> Which uses this example constantly in the book to explain queries.
> I managed to create the table by leaving the foreign keys part.
> Then altered all the tables to include the foreign keys.
>
> Now I cannot insert anything into any table!
>
> Is there any way of solving this?
>
> Thanks a lot.
>

The "obvious"(?) solution is to.....

DISABLE the primary & foreign keys.
Load the data into the tables.
ENABLE the primary & foreign keys. Received on Sun May 18 2003 - 15:23:38 CDT