Re: Foreign key in Oracle Sql

Silver wrote:

> Hi everybody and happy new year,
>
> I was wondering this:
>
> suppose we have 3 tables called 'rental', 'tape' and 'user'. The table
> rental contains 3 columns, 'userID', 'tapeID' and 'date'. Now, all those 3
> columns make up the primary key of the table 'rental'. How do I define this
> table in sql ?
> Do I have to include the constraint PRIMARY KEY as well as the constraint
> REFERENCES , iike this:
>
> CREATE TABLE Rental (
> userID number(4) constraint fk_userid references USER(userID),
> tapeID number(4) constaint fk_tapeid referenes TAPE(tapeID),
> dateOfRental date,
>
> constraint pk_rental (userID, tapeID, dateOfRental)
>
> Thanks!

While you can define constraints in your CREATE TABLE it is generally a bad practice as it limits your flexibility. Far better to use ALTER TABLE to create the constraints.

CREATE TABLE rental (
userid NUMBER(4),
tapeid NUMBER(4),
dateofrental DATE)
PCTFREE ...
PCTUSED ...
TABLESPACE ...; ALTER TABLE rental
ADD CONSTRAINT pk_rental
PRIMARY KEY (userid, tapeid, dateofrental) INITIALLY DEFERRED DEFERRABLE
USING INDEX
PCTFREE 0
TABLESPACE ...; etc.

For one thing, as above, you can make the constraints deferrable.

For full syntax go to http://www.psoug.org click on Morgan's Library
click on Table and Constraints

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---