A foreign key is a field in one table that is also the primary key of another table

Improve Article

Nội dung chính Show

Is a foreign key a primary key in another table?
Can foreign and primary key be same?
Can a column be primary and foreign key both?

Save Article

Read

Discuss

Improve Article

Save Article

Primary Key: A primary key is used to ensure that data in the specific column is unique. A column cannot have NULL values. It is either an existing table column or a column that is specifically generated by the database according to a defined sequence.

Example: Refer the figure –
STUD_NO, as well as STUD_PHONE both, are candidate keys for relation STUDENT but STUD_NO can be chosen as the primary key (only one out of many candidate keys).

Foreign Key:
A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It is a column (or columns) that references a column (most often the primary key) of another table.

Example: Refer the figure –
STUD_NO in STUDENT_COURSE is a foreign key to STUD_NO in STUDENT relation.

Figure:

Let’s see the difference between Primary Key and Foreign Key:

S.NO.	PRIMARY KEY	FOREIGN KEY
1	A primary key is used to ensure data in the specific column is unique.	A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables.
2	It uniquely identifies a record in the relational database table.	It refers to the field in a table which is the primary key of another table.
3	Only one primary key is allowed in a table.	Whereas more than one foreign key are allowed in a table.
4	It is a combination of UNIQUE and Not Null constraints.	It can contain duplicate values and a table in a relational database.
5	It does not allow NULL values.	It can also contain NULL values.
6	Its value cannot be deleted from the parent table.	Its value can be deleted from the child table.
7	It constraint can be implicitly defined on the temporary tables.	It constraint cannot be defined on the local or global temporary tables.

Problem:

You want to create a foreign key for a table in a database.

Example:

We would like to create a table named student that contains a foreign key that refers to the id column in the table city.

Solution 1 (new table):

CREATE TABLE student (
id INT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
city_id INT FOREIGN KEY REFERENCES city(id)
);

Discussion:

To create a new table containing a foreign key column that references another table, use the keyword FOREIGN KEY REFERENCES at the end of the definition of that column. Follow that with the name of the referenced table and the name of the referenced column in parentheses.

In our example, we create the table student using a CREATE TABLE clause. We list the columns’ names and put their respective data types in parentheses. The column city_id is the foreign key in this table and indicates the value of the ID stored in the column id in the table city. We write FOREIGN KEY REFERENCES at the end of the definition of this column and follow it with the referenced table and column: city(id).

Keep in mind that you can create more than one foreign key for a table.

Solution 2 (new table):

CREATE TABLE student (
id INT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
city_id INT,
FOREIGN KEY (city_id) REFERENCES city(id)
);

Discussion:

Another way to define a foreign key during table creation is to use the FOREIGN KEY REFERENCES clause at the end of the column definitions. In this case, after the FOREIGN KEY clause, we designate the foreign key column. Next comes the REFERENCES clause along with the name of the referred table and column.

You can create foreign keys on more than one column, as shown below:

Solution 3 (new table):

CREATE TABLE student (
id INT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
score_id INT,
subject_id INT,
      CONSTRAINT fk_student_score_subject_id 
      FOREIGN KEY (subject_id, score_id) REFERENCES score_subject(subject_id, score_id)
);

In this example, the constraint fk_student_score_subject_id is a foreign key consisting of two columns: score_id and subject_id. These two foreign key columns refer to two columns in the table score_subject – score_id and subject_id.

Here’s another example:

Solution 4 (new table):

CREATE TABLE student (
id INT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
city_id INT,
      CONSTRAINT fk_student_city_id 
      FOREIGN KEY (city_id) REFERENCES city(id)
);

Discussion:

In this code, we again have the CONSTRAINT clause with the name of this constraint. Use names that are easy to read and understand. In our example, we use the name fk_student_city_id, which indicates the relevant table and column. Next, we write FOREIGN KEY and add (in parentheses) the name of the column that becomes the foreign key. Then we have the REFERENCES clause followed by the name of the referenced table and column (here: id).

Solution 5 (existing table):

ALTER TABLE student
ADD FOREIGN KEY (city_id) REFERENCES city(id);

Discussion:

It is also possible to add a new foreign key to an existing table. Here, the table is altered using an ALTER TABLE clause. The table name (in our example, student) is placed after the ALTER TABLE keyword. Next, the ADD FOREIGN KEY clause is followed by the name of the column that will be used as the foreign key. Then we have the REFERENCES clause with the name of the referenced table and the name of the primary key column in parentheses.

Note that the table you’re modifying must exist before this command is executed.

Solution 6 (existing table, foreign key constraint):

ALTER TABLE student
      ADD CONSTRAINT fk_student_city_id 
      FOREIGN KEY (city_id) REFERENCES city(id)

Discussion:

Use a query like this if you want to name a foreign key column as a constraint for an existing table. Here, the foreign key constraint is named fk_student_city_id. If you do not specify the constraint name, the database generates a default constraint name (which will vary by database).

Is a foreign key a primary key in another table?

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

Can foreign and primary key be same?

Foreign keys are almost always "Allow Duplicates," which would make them unsuitable as Primary Keys. Instead, find a field that uniquely identifies each record in the table, or add a new field (either an auto-incrementing integer or a GUID) to act as the primary key.