Referential Integrity

[curlydog]

Apologies for the simplicity of this question, but it's driving me mad.

Can anyone tell me what is wrong with this sql statement.

create table Quote(quoteID varchar(10) primary key, customerID varchar(10) references Customer on delete restrict on update cascade, quoteDate date not null)

Yes the table Customer already exists. I've taken out "on delete restrict on update cascade" and the table is created, so obviously this part is causing the problem but I can't see why.

The error I'm getting is that a keyword is missing.

TIA
Curlydog

[skhanal]

What is your DBMS?. If it is SQL Server, "Restrict" is not supported.

[curlydog]

It's an Oracle database. I am aware that some do not support "restrict" & "Delete". I was told that "triggers" are used and that it was ok to use restrict & delete.

The work I'm doing is part of a university course and our instructors have told us to use restrict & delete. I thought that perhaps there was a problem with the structure of my statement, although I can't see what.

[edorne]

I had the same problem (i was using Oracle 8.1.7) and had to figure it out by myslef looking through documentation (which btw sucks for Oracle). Anyways the answer is as following (if yours behaves the same way as oracle 8.1.7):

by DEFAULT the "on delete restrict" option is used in Oracle. In other words if you try to delete a parent record, and the primary key of that record is accessed by the foreign key(s) of any children, the application will not allow you to delete that parent record EVEN if you do not have any constraints explicitly written . You must write something like ON DELETE CASCADE (or other referential integity constraints) if you wish to delete a parent record refernced by children records. Otherwise if you want to have on delete restrict just leave this message out , and the system will take care of that for you.