Re: How to create tables referenced each other

The book has a cd with a few commercial rdbms. I expected the example database has been set up once I installed one rdbms. I just finished Oracle installation on my NT. There is no example database; In order to learn the book, I have to create a database myself. The sql given with the book doesn't work. That is what I posted.

    After I read Chapter 13 Creating a Database, I moved foreign key out of table definitions and use 'Alter table tableName add foreign key ...' to add foreign keys. Now I can create all five tables.

    I appreciate your help and looking for a decent book about sql. Who can recommend a decent sql book?

Jim

"Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3F030341.7A567225_at_exxesolutions.com...
> 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)
>
>
Received on Wed Jul 02 2003 - 13:12:33 CDT