Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    7

    Question Unanswered: Assigning FOREIGN KEY, which method is correct?

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No difference - except that you couldn't create a composite foreign key with the first method. They are just alternatives.

  3. #3
    Join Date
    May 2004
    Posts
    7

    Question

    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.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •