Re: How to create tables referenced each other

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

These table definitions are horrible. I use examples like this when teaching my students what NEVER to do.

CHAR(3)? Never ... always use VARCHAR2(3) Defining constraints in a table definition without specifying constraint names, storage, tablespace ... yech!

Get another book. Seriously ... get another book.

A referential constraint between tables is a foreign key and the above creates a few so referential integrity has been built into what you posted. It is just that it is about the ugliest examples I have seen in years. I think your problem is one of, perhaps, not understanding them to do what you expect or how to use them.

Get a decent book and it will solve multiple problems. The least of which will be not teaching you bad habits.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)