How do you create a foreign key in the derby database proveided in netebeans 5.0. I have tried the following statement:
ALTER TABLE staff ADD CONSTRAINT staff_fk FOREIGN KEY (staffID) REFERENCES staff (staffID);
I get the error: staffID is not a colum in Table of VTI skill
please help- I just want to create a foreign key in my dataabse any ideas welcome
thank you
I am using Derby 10.1 and NetBeans IDE 5.0.
The Derby 10.1 documentation is unclear about FOREIGN KEY as a column constraint, but suggests that it also can be a table constraint. SQL-92 syntax does not allow FOREIGN KEY as a column constraint. Derby 10.1 "implements an SQL-92 core subset, as well as some SQL-99 [a.k.a. SQL3] features."
According to SQL-92, FOREIGN KEY can only be part of a <referential constraint definition>. The Derby 10.1 documentation is consistent with SQL-92 in the Derby Reference Manual on its "Table-level constraint" page. You wrote
FOREIGN KEY (staffID) REFERENCES staff (staffID)
That looks like a table-level constraint. I don't know what "of VTI skill" is, but I have one idea that you can try:
FOREIGN KEY (staffID) REFERENCES staff
is legal SQL-92 syntax for a <referential constraint definition>. It eliminates one instance of "staffID", which appears in your error message. Therefore, my idea is part of a divide-and-conquer approach to debugging.
Alan Feldstein
Cosmic Horizon
http://www.alanfeldstein.com/
