Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Lightbulb Unanswered: Composite Primay Key and foreign Key

    Hi all,

    I get a big problem when I doing my assignment.
    It told me to:

    - Create a table with one composite primary key
    - Create another table with one primary key and one foreign key refer to last table

    Here is my SQL statement
    CREATE TABLE table1
    (
    FirstName varchar(10)
    LastName varchar(10)
    PRIMARY KEY (FirstName, LastName)
    )

    CREATE TABLE table2
    (
    PlanID int,
    PRIMARY KEY (PlanID)
    FirstName varchar(10) FOREIGN KEY REFERENCES table1(FirstName)
    LastName varchar(10) FOREIGN KEY REFERENCES table1(LastName)
    )

    It failed. I have searched for google long time but no result for that.
    Hope you can save me. Thanks!!!

  2. #2
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    Your problem is probably caused because you reference a column to a partial key. What exact error do you get?

    Another solution could be:
    Code:
    CREATE TABLE table1
    (
    keyField int not null
    FirstName varchar(10)
    LastName varchar(10)
    PRIMARY KEY (keyField)
    )
    
    CREATE TABLE table2
    (
    PlanID int not null,
    PRIMARY KEY (PlanID)
    t1_keyField int FOREIGN KEY REFERENCES table1(keyField)
    )
    If you want the combination of the first and last name to be unique you can always create a unique constraint on the two columns.
    Johan

  3. #3
    Join Date
    Nov 2003
    Posts
    4
    [QUOTE][SIZE=1]Originally posted by jora
    Your problem is probably caused because you reference a column to a partial key. What exact error do you get?

    The error said
    "There are no primary or candidate keys in the referenced table 'table1'
    that match the referencing column list in the foreign key 'FK_table2_FirstName'

    Thanks for your suggestion but table1 should have a composite primary key instead of a single primary key.

  4. #4
    Join Date
    Nov 2003
    Posts
    4
    Oh! I get it.

    Here is the code:
    CREATE TABLE table1
    (
    FirstName varchar(10),
    LastName varchar(10),
    PRIMARY KEY (FirstName, LastName),
    )

    CREATE TABLE table2
    (
    PlanID int,
    FirstName varchar(10),
    LastName varchar(10),
    PRIMARY KEY (PlanID),
    FOREIGN KEY(FirstName,LastName) REFERENCES table1 FirstName,LastName),
    )

Posting Permissions

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