Hi guys,

I am real new to SQL and just like to clear up some questions regarding to assigning FOREIGN KEY.

Are there any differences in assigning foreign keys this way:

CREATE TABLE NURSE
(EMP_ID VARCHAR2(8) CONSTRAINT fk_nurse_emp REFERENCES EMPLOYEE(EMP_ID),
WARD_ID VARCHAR2(8) CONSTRAINT fk_nurse_ward REFERENCES WARD(WARD_ID),
CONSTRAINT pk_nurse PRIMARY KEY(EMP_ID, WARD_ID));

and this way:

CREATE TABLE NURSE
(EMP_ID VARCHAR2(8),
WARD_ID VARCHAR2(8),
CONSTRAINT fk_nurse_emp FOREIGN KEY(EMP_ID) REFERENCES EMPLOYEE(EMP_ID),
CONSTRAINT fk_nurse_ward FOREIGN KEY(WARD_ID) REFERENCES WARD(WARD_ID),
CONSTRAINT pk_nurse PRIMARY KEY(EMP_ID, WARD_ID));

What are the differences between them?

Many thanks.

No difference - except that you couldn't create a composite foreign key with the first method. They are just alternatives.

Thank you for clearing this up for me andrewst.

Another question, might not be all that important, but which is the preferred method?

I guess the second method is more preferred, as it clearly identifies the FOREIGN KEYS, but it takes more lines. I just like to learn the basic conventions first and stick to it.

Well it's really a matter of personal taste and/or company policy. But since the second syntax works for all foreign keys and the first works only for single-column foreign keys, the second syntax could be preferred.