Re: How to insert values?

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. Received on Sun May 18 2003 - 14:48:34 CDT