Re: Foreign key in Oracle Sql

Just for the record if you remove the "on update cascade" lines and create the table users and tapes with PK the code will work: (ver 9.2.0.5 on AIX 5.2)

UT1 > create table tapes (tape_id number primary key) 2 /

Table created.

UT1 > CREATE TABLE Rentals
2 (user_id NUMERIC (4) REFERENCES Users(user_id), 3 tape_id NUMERIC (10) REFERENCES Tapes (tape_id) 4 ON DELETE CASCADE,
5 rental_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, 6 PRIMARY KEY (user_id, tape_id, rental_date ));

Table created.

But as Daniel says it is generally best to generate source files so that constraints are created via ALTER TABLE commands. This provides greater flexibility when maintenance operations are necessary. It also helps maintain clear documentation especially when storage clauses and index parameters are added.
Just adding to the confusion.
-- Mark D Powell -- Received on Fri Jan 07 2005 - 15:20:25 CET