what is FOREIGN KEY is for?

Question

let's say i have 2 tables:

Department(depNum)
Worker(id,deptNum) - key should be id

now i want dept to reference an existing value in Department. so i write in create table:

CREATE TABLE Worker(
id integer primary key,
dept integer references Department);

my question is, i've seen in many examples that you also put foreign key with the references statement. i don't understand what is primary key for. does it mean that dept will be also a key on Worker?

thank you

Your worker table should have dept_id, not dept. The syntax used for 'references' and 'foreign key' can vary a bit from SQL implementation to SQL implementation. — Michael Durrant, Nov 29 '11 at 18:20
some implementations may let you use `dept references Department` but in those cases you may want to drop `integer` — Michael Durrant, Nov 29 '11 at 18:23

score 1 · Answer 1 · answered Nov 29 '11 at 18:23

From Wikipedia:

A primary key is a combination of columns which uniquely specify a row. It is a special case of unique keys. . . . Primary keys were added to the SQL standard mainly as a convenience to the application programmer.

You cannot reference a record in a table without a primary key. A foreign key lets you reference a record in another table within an individual record. This foreign key is usually referencing the primary key in the foreign table.

score 1 · Answer 2 · edited May 23 '17 at 11:48

1

This post has a lot of great information. In particular, check out the highest ranked answer for a bullet list of do's and do not's.

What's wrong with foreign keys?

This post gives a pretty decent explanation, given the poster's original example:

What will these foreign keys do?

edited May 23 '17 at 11:48

Community

1
1

answered Nov 29 '11 at 18:25

Lynn Crumbling

12,985
8
57
95

score 1 · Answer 3 · answered Nov 29 '11 at 18:26

Let's say that each worker can only work in one department at any one time. So each department has its own unique ID. This is the department's primary key because two departments should never have the same id.

Now, each individual worker must be tracked so they are also assigned their own unique ID. This is their primary key. You need to link the worker to the department that they work in and since they can only work in one department at a time, you can have their department as a foreign key. The foreign key in the worker table is linked to the ID of the department table.

This has more information: http://www.1keydata.com/sql/sql-foreign-key.html

Christian Kuetbach · Accepted Answer · 2011-11-29T18:34:28.923

You have two tables:

PLAYER, 
primary key (unique) PK_player_id
player_name
foreignt key to TEAM.team_id FK_team_id

TEAM
primary key (unique) PK_team_id
team_name

Every PLAYER is in exact one TEAM.

The PLAYER has a FOREIGN-KEY to the TEAM (FK_team_id). Also you can delete the TEAM, which will delete all player in it cascading (if configured).

Now you can't create a player without an existing TEAM, because the database ensures this.

EDIT: Didn't you ask for the foreign key?

The primary key is one or more than one column, which will identify on datarow within your database. If jou want to create a foreign key, you have to use a column or more than one column) which is unique.

In my example, there is a unique key (the primary key) for every table, because the name may change. To identify the 'target' of the foreign key, it has to be unique. so it is liklye to use the prmary key of the second table. (TEAM.PK_team_id)

score 0 · Answer 5 · answered Nov 29 '11 at 18:26

I am not clear, the requirement should be

Department(dept) Worker(id,dept) - key should be id

which means dept is the primary key in Department and foreign key in worker. the foreign key is not unique in worker table but it is unique in Department Table.

The worker table cannot have some unknown department which is not defined in the Department.

Did I make sense ?

score 0 · Answer 6 · answered Nov 29 '11 at 18:32

0

To ensure the integrity of the tables, not allowing you to enter values in the table (Worker) without referencing an existing row (at Department)

answered Nov 29 '11 at 18:32

Marco Moraes

21
1

score 0 · Answer 7 · answered Nov 30 '11 at 09:21

According to the SQL-92 Standard:

A foreign key (FK) may reference either a PRIMARY KEY or a UNIQUE CONSTRAINT. In the case of a PRIMARY KEY, the referenced columns may be omitted from the foreign key declaration e.g. the following three are all valid:

CREATE TABLE Department ( Department INTEGER NOT NULL PRIMARY KEY, ...);
CREATE TABLE Worker (dept INTEGER REFERENCES Department, ...);

CREATE TABLE Department ( Department INTEGER NOT NULL PRIMARY KEY, ...);
CREATE TABLE Worker (dept INTEGER REFERENCES Department (dept), ...);

CREATE TABLE Department ( Department INTEGER NOT NULL UNIQUE, ...);
CREATE TABLE Worker (dept INTEGER REFERENCES Department (dept), ...);

The following is not valid:

CREATE TABLE Department ( Department INTEGER NOT NULL UNIQUE, ...);
CREATE TABLE Worker (dept INTEGER REFERENCES Department, ...);

...because the referenced columns involved in the foreign key must be declared.

When declaring a simple (single-column) FK in-line the FOREIGN KEY keywords are omitted as above.

A composite (multiple-column) cannot be declared in-line and a simple FK need not be declared in-line: in these cases, the referencing column(s) AND the FOREIGN KEY keywords are required (the rules for the referenced columns remain the same as stated earlier) e.g. here are just a few examples:

CREATE TABLE Department ( Department INTEGER NOT NULL PRIMARY KEY, ...);
CREATE TABLE Worker (dept INTEGER, FOREIGN KEY (dept) REFERENCES Department, ...);

CREATE TABLE Department ( Department INTEGER NOT NULL UNIQUE, ...);
CREATE TABLE Worker (dept INTEGER, FOREIGN KEY (dept) REFERENCES Department (dept), ...);

CREATE TABLE DepartmentHistory 
(
 dept INTEGER NOT NULL, 
 dt DATE NOT NULL,
 PRIMARY KEY (dt, dept), 
 ...
);
CREATE TABLE Worker
(
 dept INTEGER NOT NULL, 
 dept_dt DATE NOT NULL, 
 FOREIGN KEY (dept_dt, dept) REFERENCES DepartmentHistory, 
 ...
);

what is FOREIGN KEY is for?

7 Answers7

Linked