Re: Foreign key in Oracle Sql

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Thu, 06 Jan 2005 14:25:07 -0800
Message-ID: <41ddb995$1_4@127.0.0.1>

-CELKO- wrote:

>>>How do I define this table in SQL ? <<

>
>
> Using Standard SQL and ISO-11179 data elemet names, you want something
> like this:
>
> CREATE TABLE Rentals
> (user_id NUMERIC (4) REFERENCES Users(user_id)
> ON UPDATE CASCADE,
> tape_id NUMERIC (10) REFERENCES Tapes (tape_id)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> rental_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
> ... -- other stuff, price, dues date, etc.
> PRIMARY KEY (user_id, tape_id, rental_date ));
>
> Yes, you have to include the natural primary key.
>
> I would make tape_id into a UPC code plus an accession number, so you
> can track the copies of each tape as well as the titles you stock.

Good try Joe ... but not in Oracle. Not only is it not syntactically correct ... it would not be best practice. Anyone specifying index creation in Oracle, which a primary key will always do if a usable index does not already exist, should be specifying PCTFREE and preferably at 0 so as not to waste space and decrease performance.

SQL*Plus: Release 10.1.0.3.0 - Production on Thu Jan 6 14:23:33 2005

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLE Rentals

   2 (user_id NUMERIC (4) REFERENCES Users(user_id)    3 ON UPDATE CASCADE,
   4 tape_id NUMERIC (10) REFERENCES Tapes (tape_id)    5 ON UPDATE CASCADE
   6 ON DELETE CASCADE,
   7 rental_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,    8 PRIMARY KEY (user_id, tape_id, rental_date )); ON UPDATE CASCADE,
    *
ERROR at line 3:
ORA-00905: missing keyword

SQL> In Oracle it is always preferable to create constraints using ALTER TABLE.

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

Received on Thu Jan 06 2005 - 16:25:07 CST