CODEWITHSUNDEEP
mysqlsqldatabasekey
user1911467
user1911467

Reputation: 61

Non-unique foreign key

Do foreign keys have to be unique?

I'm trying to create a table that stores the foreign key that references to a user and a column 'profileIconId'. The purpose of the table is to have a list of icons that the user owns. I would like to use cascade delete.

My other choice is to use SELECT FROM WHERE to retrieve the list and use DELETE FROM WHERE to delete all rows that matches the key when the user is removed.

Upvotes: 1

Views: 7020

Answers (5)

Jason
Jason

Reputation: 2820

Foreign key(s) must reference a unique set of attributes in the referenced table. So, your foreign keys: user and profileIconId don't need to be unique, but what they reference does.

Consider the following schema:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  ... omitted ...
);

CREATE TABLE icons (
  id INTEGER PRIMARY KEY,
  ... omitted ...
);

CREATE TABLE user_icons (
  user INTEGER REFERENCES users,
  profileIconId INTEGER REFERENCES icons
);

The user and profileIconId values do not need to be unique in the user_icons table, but id in users and icons must be unique (or technically a candidate primary key).

Note, the referenced columns in this case must be unique to qualify as Primary Keys.

This would be an acceptable way to accomplish the goal of creating a table that fulfills the user has zero or many icons relationship.

Upvotes: 1

pistou
pistou

Reputation: 2867

No, Foreign Key in a table doesn't have to be unique in said table.

BUT, as it is the Primary Key of another table, it must be unique in this table.

Upvotes: 4

Jeff Rosenberg
Jeff Rosenberg

Reputation: 3572

No, they don't. In fact, one of the most common uses of a foreign key is a one-to-many relationship, such as between Customers and Orders, for example.

Upvotes: 7

eggyal
eggyal

Reputation: 125835

No, foreign keys do not have to be unique. Indeed, a lack of uniqueness is requisite for one-to-many or many-to-many relations.

Upvotes: 3

John Woo
John Woo

Reputation: 263693

No.

But the values must exists first on the parent table before you can insert it on the table.

Upvotes: 3

Related Questions