How to create tables referenced each other

[Jim]

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]

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
[email]oracle_dba@remove_spam.peasland.com[/email]

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"

[Daniel Morgan]

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
[url]http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp[/url]
[email]damorgan@x.washington.edu[/email]
(replace 'x' with a 'u' to reply)

[Karsten Farrell]

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]

[Anurag Varma]

"Brian Peasland" <oracle_dba@remove_spam.peasland.com> wrote in message
news:3F0300BF.5009A707@remove_spam.peasland.com...

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

Brian,

A minor correction:
As tested on oracle 9.2.0.3: on delete set null is *valid*.
Though "on delete restrict" is still invalid.

Here is an example:
SQL> create table t1 (a number, b varchar2(3), constraint a_pk primary key
(a));
Table created.

SQL> create table t2 (a number, b varchar2(6), constraint a_fk foreign key
(a) references t1 on delete set null);
Table created.

SQL> insert into t1 (a, b) values (1,'abc');
1 row created.

SQL> insert into t1 (a, b) values (2,'xyz');
1 row created.

SQL> insert into t2 (a,b) values (1,'abc');
1 row created.

SQL> select * from t1;
A B
---------- ---
1 abc
2 xyz

SQL> select * from t2;
A B
---------- ------
1 abc

SQL> delete from t1 where a = 1;
1 row deleted.

SQL> select * from t1;
A B
---------- ---
2 xyz

SQL> select * from t2;
A B
---------- ------
abc


Anurag

[Daniel Morgan]

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@exxesolutions.com> wrote in message
> news:3F030341.7A567225@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
> > [url]http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp[/url]
> > [email]damorgan@x.washington.edu[/email]
> > (replace 'x' with a 'u' to reply)
> >
> >

Look at Oracle8 How-To from Waite Group Press.

--
Daniel Morgan
[url]http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp[/url]
[email]damorgan@x.washington.edu[/email]
(replace 'x' with a 'u' to reply)

[Brian Peasland]

The book that I first learned Oracle SQL from was Oracle, The Complete
Reference on Oracle Press. It does a good enough job, but I'm not sure
that I'd give it an A+.

HTH,
Brian

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@exxesolutions.com> wrote in message
> news:3F030341.7A567225@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
> > [url]http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp[/url]
> > [email]damorgan@x.washington.edu[/email]
> > (replace 'x' with a 'u' to reply)
> >
> >

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

Brian Peasland
[email]oracle_dba@remove_spam.peasland.com[/email]

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"