Re: How to create tables referenced each other

Jim was kind enough to write:
> I had 'SQL the complete reference' authored by James R. Groff. I can not
> believe it I can not find email or home page for this book or authors. Now I
> have a question about how to create tables referenced each other. I have a
> sql from the book. It doesn't work on Oracle. I cite it as following:
>
> CREATE TABLE CUSTOMERS
> (CUST_NUM INTEGER NOT NULL,
> COMPANY VARCHAR(20) NOT NULL,
> CUST_REP INTEGER,
> CREDIT_LIMIT NUMERIC(16,6),
> PRIMARY KEY (CUST_NUM),
> FOREIGN KEY HASREP (CUST_REP)
> REFERENCES SALESREPS
> ON DELETE SET NULL),
>
> CREATE TABLE OFFICES
> (OFFICE INTEGER NOT NULL,
> CITY VARCHAR(15) NOT NULL,
> REGION VARCHAR(10) NOT NULL,
> MGR INTEGER,
> TARGET NUMERIC(16,6),
> SALES NUMERIC(16,6) NOT NULL,
> PRIMARY KEY (OFFICE),
> FOREIGN KEY HASMGR (MGR)
> REFERENCES SALESREPS
> ON DELETE SET NULL),
>
> CREATE TABLE SALESREPS
> (EMPL_NUM INTEGER NOT NULL,
> NAME VARCHAR(15) NOT NULL,
> AGE INTEGER,
> REP_OFFICE INTEGER,
> TITLE VARCHAR(10),
> HIRE_DATE DATE NOT NULL,
> MANAGER INTEGER,
> QUOTA NUMERIC(16,6),
> SALES NUMERIC(16,6) NOT NULL,
> PRIMARY KEY (EMPL_NUM),
> FOREIGN KEY (MANAGER)
> REFERENCES SALESREPS
> ON DELETE SET NULL,
> FOREIGN KEY WORKSIN (REP_OFFICE)
> REFERENCES OFFICES
> ON DELETE SET NULL),
>
> CREATE TABLE ORDERS
> (ORDER_NUM INTEGER NOT NULL,
> ORDER_DATE DATE NOT NULL,
> CUST INTEGER NOT NULL,
> REP INTEGER,
> MFR CHAR(3) NOT NULL,
> PRODUCT CHAR(5) NOT NULL,
> QTY INTEGER NOT NULL,
> AMOUNT NUMERIC(16,6) NOT NULL,
> PRIMARY KEY (ORDER_NUM),
> FOREIGN KEY PLACEDBY (CUST)
> REFERENCES CUSTOMERS
> ON DELETE CASCADE,
> FOREIGN KEY TAKENBY (REP)
> REFERENCES SALESREPS
> ON DELETE SET NULL,
> FOREIGN KEY ISFOR (MFR, PRODUCT)
> REFERENCES PRODUCTS
> ON DELETE RESTRICT),
>
> CREATE TABLE PRODUCTS
> (MFR_ID CHAR(3) NOT NULL,
> PRODUCT_ID CHAR(5) NOT NULL,
> DESCRIPTION VARCHAR(20) NOT NULL,
> PRICE NUMERIC(16,6) NOT NULL,
> QTY_ON_HAND INTEGER NOT NULL,
> PRIMARY KEY (MFR_ID, PRODUCT_ID))
>
> Please help me.
>
> Jim
>

I think your trouble is that you're asking the dbms to create a FK constraint against a table that it hasn't "seen" yet. For example, your CUSTOMERS table references the SALESREPS table before you've created it. Either (1) use DEFERRED constraints or (2) create all the tables without FK constraints, then use a series of ALTER TABLE statements to create the FKs.

-- 
[:%s/Karsten Farrell/Oracle DBA/g]