Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Smile Unanswered: Primary key and foreign key shares the same field?

    Im creating a table using mySQL with two primary keys and two foreign keys using the same field.

    itemA and itemB is a Primary key of another table but also used as the Primary key of table Recommendation. Is it possible to create foreign key and primary key using the same field? I had an error when creating this table. Please help me. Thanx a lot!

    ===========================================
    CREATE TABLE Recommendation
    (
    itemA INT NOT NULL,
    itemB INT NOT NULL,
    PRIMARY KEY(itemA),
    PRIMARY KEY(itemB),
    INDEX(itemA),
    INDEX(itemB),
    FOREIGN KEY(itemA) REFERENCES Items(itemID) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY(itemB) REFERENCES Items(itemID) ON UPDATE CASCADE ON DELETE CASCADE) TYPE=INNODB;
    ===========================================

  2. #2
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: Primary key and foreign key shares the same field?

    You can't define multiple PRIMARY keys. You can define PRIMARY keys with multiple columns: PRIMARY KEY(itemA, itemB).

  3. #3
    Join Date
    Jan 2004
    Posts
    4

    Smile Re: Primary key and foreign key shares the same field?

    i had tried this mtd but also cannot work

    ===========================
    CREATE TABLE Recommendation
    (
    itemA INT NOT NULL ,
    itemB INT NOT NULL ,
    PRIMARY KEY(itemA,itemB),
    INDEX(itemA),
    INDEX(itemB),
    FOREIGN KEY(itemA) REFERENCES Items(itemID) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY(itemB) REFERENCES Items(itemID) ON UPDATE CASCADE ON DELETE CASCADE) TYPE=INNODB;
    ===========================

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    syntax looks okay; what doesn't work? what error message did you get?

    just guessing... perhaps the fk constraints need explicit names...

    , CONSTRAINT itemA_fk FOREIGN KEY itemA_fkindex (itemA) REFERENCES Items(itemID) ON UPDATE CASCADE ON DELETE CASCADE
    , CONSTRAINT itemB_fk FOREIGN KEY itemB_fkindex (itemB) REFERENCES Items(itemID) ON UPDATE CASCADE ON DELETE CASCADE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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