CODEWITHSUNDEEP
mysqlsql
FrozenHeart
FrozenHeart

Reputation: 20746

Foreign key -- id or just UNIQUE column

Suppose that I have a table with the following columns:

CREATE TABLE users
{
  id INTEGER PRIMARY KEY,
  login VARCHAR(50) UNIQUE,
  password VARCHAR(50)
}

And I need to create a table which will store one cookie for each account. What's better -- to create a foreign key that refers to the login column from the first table

CREATE TABLE cookies
{
  id INTEGER PRIMARY KEY,
  cookie VARCHAR(50),
  login VARCHAR(50) FOREIGN KEY REFERENCES users(login)
}

or to create a foreign key that refers to the id column?

CREATE TABLE cookies
{
  id INTEGER PRIMARY KEY,
  cookie VARCHAR(50),
  expires DATETIME,
  id_user INTEGER FOREIGN KEY REFERENCES users(id)
}

And why?

Upvotes: 1

Views: 81

Answers (3)

Steve E.
Steve E.

Reputation: 9343

Use a foreign key that relates to the primary key of the users table:

create a foreign key that refers to the id column?

CREATE TABLE cookies
(
    id INTEGER PRIMARY KEY,
    cookie VARCHAR(50),
    expires DATETIME,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(id)
)

The reason for doing this as a general convention is that the users(id) primary key is an immutable value. The 'login' column or any other which represents meta data about an object external to the database may change over time and is therefore less suitable for use in foreign key references.

Upvotes: 1

Faisal Zaheer
Faisal Zaheer

Reputation: 33

CREATE TABLE cookies
{
  id INTEGER PRIMARY KEY,
  cookie VARCHAR(50),
  login VARCHAR(50) FOREIGN KEY REFERENCES users(login)
}

I think this is better option, as you need to communicate with login.

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Ok as you have several columns in cookie table and since that is a seperate entity I agree and it makes sense to take those additional columns in seperate table. What about pks and fks? Since you have only one row per user I would make userid a primary key and foreign key simultaneously:

cookies
{
  id_user(pk, fk), 
  cookie,
  expires
}

Upvotes: 3

Related Questions