CODEWITHSUNDEEP
sqlforeign-keysprimary-key
allenylzhou
allenylzhou

Reputation: 1461

Should I have two primary keys referencing each other?

CREATE TABLE1 (
   ID INT NOT NULL PRIMARY KEY
   FOREIGN KEY ID REFERENCES TABLE2 (ID)
)

CREATE TABLE2 (
   ID INT NOT NULL,
   OTHER INT NOT NULL
   PRIMARY KEY (ID, OTHER)
   FOREIGN KEY ID REFERENCES TABLE1 (ID)
)

Table 1 and Table 2 are big tables containing separate sets of information. They have a one-to-one relationship requiring full participation from both sides. Where should I put the foreign key statement? In Table1, Table2, or both? And why?

Upvotes: 0

Views: 115

Answers (2)

Bohemian
Bohemian

Reputation: 424993

There is nothing particularly wrong with doing it, however you should put the foreign key in both tables. That means when you insert new values you'll have to start a transaction, do both inserts, then commit the transaction.

I would strongly recommend merging the tables into one table. It will make everything easier.

Upvotes: 1

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

Id to id relationship is considered . This is a great trick in implementation .

Upvotes: 0

Related Questions