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;
===========================================

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

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;
===========================

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