Having issues declaring a FK in Oracle 9i. I've looked at a number of examples here on SO and in some online docs (e.g. http://www.techonthenet.com/oracle/foreign_keys/foreign_delete.php) without any real luck; trying a similar syntax to that in the link generates the same error:
Error at Command Line:19 Column:4
Error report:
SQL Error: ORA-02253: constraint specification not allowed here
02253. 00000 - "constraint specification not allowed here"
*Cause: Constraint specification is not allowed here in the statement.
*Action: Remove the constraint specification from the statement.
An excerpt of the SQL itself is as below. "Line 19" refers to the line starting with CONSTRAINT
CREATE TABLE Flight (
flight_no varchar2(10) NOT NULL,
airplane_id varchar2(20) NOT NULL
CONSTRAINT flight_airplane_id_fk FOREIGN KEY (airplane_id) REFERENCES Airplane (airplane_id)
ON UPDATE RESTRICT ON DELETE RESTRICT,
dept_date date NOT NULL,
...
Alternatively, trying it without the CONSTRAINT keyword generates an error about a right parenthesis that I can't seem to see is missing.
PS: I understand ON UPDATE RESTRICT is the default behaviour in Oracle, but I prefer to be explicit wherever possible.
Justin Cave :
First off, in Oracle, there is no ON UPDATE RESTRICT or ON DELETE RESTRICT option. Those appear to be valid in other database engines but they aren't present in the constraint syntax diagram and do not appear to be valid. There is an ON DELETE clause but the only two valid options are CASCADE or SET NULL. There is no ON UPDATE clause.\n\nIf we add a comma at the end of the airplane_id definition before the constriant definition and remove the two invalid clauses, your DDL should be valid\n\nCREATE TABLE Flight (\n flight_no varchar2(10) NOT NULL,\n airplane_id varchar2(20) NOT NULL,\n CONSTRAINT flight_airplane_id_fk \n FOREIGN KEY (airplane_id) REFERENCES Airplane (airplane_id),\n dept_date date NOT NULL,\n <<more columns>>\n);\n",
2011-11-03T14:16:11