Re: How to create tables referenced each other

Jim wrote:

> 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)
> >
> >

Look at Oracle8 How-To from Waite Group Press.

--
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)