Re: How to create tables referenced each other

The ON DELETE SET NULL and ON DELETE RESTRICT clauses are not valid for Oracle. And it would really help if you would run each statement and post any and all error messages you are receiving.

HTH,
Brian

Jim wrote:
>
> 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

-- 
===================================================================

Brian Peasland
oracle_dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"