A foreign key is simply a primary key of another table in a table. A foreign key is an entity (column) (or a set of entities (columns)) in a table that establishes a relationship with the primary key of another table. For instance table o has column A as a primary key, if column A is found in another table say k, it is called a foreign key and it is used to establish a relationship between table o and k.
READ ABOUT HOW TO CREATE A TABLE & PRIMARY KEY IN SQL
Let’s create two tables named seat and order, before then let’s create a database and make use of the database
create database restaurant;
use restaurant;create table seat (
seat_id INT(11),
Location varchar(255),
primary key (seat_id)
);Let’s create another table called order
CREATE TABLE order(
order_id INT,
date_time DATETIME,
seat_id INT,
PRIMARY KEY (order_id),
FOREIGN KEY (seat_id) REFERENCES seat(seat_id),
); from the tables above, seat_id is the primary key for the seat table and seat_id is a foreign key in order table therefore seat_id is the column that connects the seat and order table together. Note: Foreign keys can contain null values
CANDIDATE KEY
A candidate key is a entity (column) (or a set of entities) that can uniquely identify a row in a table. A candidate key must be unique, which means it must uniquely identify a row in a table. A candidate key cannot be empty (null) unlike a foreign key. A table can have multiple candidate keys, but only one of them is chosen as the primary key.
Let’s create a tabe called workers with two candidate (unique) keys
CREATE TABLE Workers (
Worker_ID INT UNIQUE,
Email VARCHAR(100) UNIQUE,
Name VARCHAR(50),
Department VARCHAR(50)
);Notice that both Worker_ID and Email have the UNIQUE key meaning that both columns are candidate keys.
