CODEWITHSUNDEEP
mysqlsqlforeign-keysrelational-database
Ignatius Ojiambo
Ignatius Ojiambo

Reputation: 131

Can a table in SQL have multiple columns as foreign keys that refer only to one primary key of another table?

For instance, table Companies has columns company_name, first_contact, second_contact and table contacts have columns id(PK), name, phone. Can a table (companies) in SQL have multiple columns as foreign keys that refer only to one primary key of another table (contacts)?

Upvotes: 1

Views: 1064

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562250

Yes, here's an example:

mysql> CREATE TABLE Contacts (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE Companies (id INT PRIMARY KEY, company_name TEXT,
    -> first_contact INT, second_contact INT,
    -> FOREIGN KEY (first_contact) REFERENCES Contacts(id),
    -> FOREIGN KEY (second_contact) REFERENCES Contacts(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

But it would be more common to design the database another way, with a third table instead of the two foreign keys in Companies:

mysql> CREATE TABLE CompanyContacts (
    -> contact_id INT NOT NULL,
    -> company_id INT NOT NULL,
    -> is_primary BOOL NOT NULL,
    -> PRIMARY KEY (contact_id, company_id),
    -> FOREIGN KEY (contact_id) REFERENCES Contacts(id),
    -> FOREIGN KEY (company_id) REFERENCES Companies(id)
    -> );
Query OK, 0 rows affected (0.04 sec)

Some advantages:

  • You aren't limited to two contacts per company.
  • You can search for a contact more simply — instead of searching if a contact occurs as either the first_contact or second_contact, you just search for it in CompanyContacts.contact_id. It's easier to optimize that query with an index.

Some disadvantages:

  • No way to make a constraint to make at least one contact mandatory. You can do this by declaring first_contact as NOT NULL in your design, but there's no SQL constraint that requires a row to exist in the third table for each company.
  • If you're put off by JOIN queries, this might not be attractive. But I recommend you get comfortable with doing JOINs when you have many-to-many relationships.

Upvotes: 2

Lawrence
Lawrence

Reputation: 347

Sure! You'd essentially have several one-to-one relationships from Companies to Contacts.

When querying the data, you'd have to join in the Contacts table multiple times (once per column that is a foreign key)

select *
from Companies c
join Contacts contact1 on c.first_contact=contact1.id
join Contacts contact2 on c.second_contact=contact2.id

Upvotes: 0

Related Questions