PRIMARY and FOREIGN KEY
PRIMARY KEY (PK)
Looking back at our example table "students", we know that students can have same names, either First or Last or Both. And, identifying each students with their email can be convoluted as well. So, we provided a Unique Student_ID to each student. This means that the column "student_id" uniquely defines each rows of the table "students". Such column can be considered to be a Primary Key(PK).
Features of Primary Key (PK) Constraint:
PK constraint uniquely represents each row in a table,
It must contain UNIQUE values,
It cannot contain NULL values,
A table can have only one PK,
PK, however, can consist of multiple columns.
Creating PRIMARY KEY Constraint
We can create a PK constraint while creating the table using following syntax:
CREATE TABLE students (
student_id INT NOT NULL,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
email VARCHAR(255),
PRIMARY KEY (student_id)
);
If we have already created a table and would like to add PRIMARY KEY constraint to a column in that table, we can use ALTER TABLE as follow:
ALTER TABLE students (
ADD PRIMARY KEY (student_id)
);
We can also manually add PRIMARY KEY, once the table is created in MySQL WorkBench. It will generate the ALTER TABLE query as mentioned above automatically for us. Please refer to the procedure shown in figure below:
Having said that, we can now guess what is the Primary Key in the table "games". Ofcourse, it is "Game_ID", since it uniquely defines each games in the table.
FOREIGN KEY (FK)
In the "games" table, we have participants initials. It is obvious that one student can participate in many games. Like, SS has participated in Basketball, as well as Football. Similarly, JS has participated in Chess and Hockey. However, JS can be John Smith, or Jenny Smith. So to refer the correct participant, we can provide another constraint called Foreign Key (FK). Here, "Student_ID" is a Foreign Key that links the table games to the table students.
Features of Foreign Key (FK) Constraint:
It is used to link two tables
A Foreign Key in one table, is a Primary Key in another table that is linked with it
PK and FK develop parent-child relationship with tables
FK in the table does not have to be unique. (For example, in games table, one student can participate in two or more games so student_id (FK) is repeated.
Creating FOREIGN KEY Constraint
We can create a FK constraint while creating the table using following syntax:
CREATE TABLE games(
game_id INT NOT NULL,
game_name VARCHAR(255) NOT NULL,
participant VARCHAR(255) NOT NULL,
student_id INT,
PRIMARY KEY (game_id)
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
Similarly, if we have already created a table and would like to add FOREIGN KEY constraint to a column in that table, we can use ALTER TABLE as follow:
ALTER TABLE games
ADD FOREIGN KEY (student_id) REFERENCES students(student_id);
We can also manually add FOREIGN KEY, once the table is created in MySQL WorkBench. It will generate the ALTER TABLE query as mentioned above automatically for us. Please refer to the procedure shown in figure below:
The following figure represent the relationship between two tables that we created with Primary Key and Foreign Key constraints. It is also important to understand the different types of relationship that exists between tables in SQL. You can refer to ERD section for detailed information.
