How to Create Primary Key and Foreign Key Constraints on a Table in SQL

SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database Table.Primary Keys must contain unique values.

A Primary Key column will not allow duplicates and NULL values. Each table should have a Primary Key, and each table can have only One Primary Key. One we create a Table with Primary Key, a default Cluster Index will be created for the Table based on that Column.

Defining PRIMARY KEY Constraint  with CREATE TABLE Statement :
The following SQL statement creates a PRIMARY KEY on the "Customer_Id" column when the "Customers" table is created:

SQL Server / Oracle / MS Access:
CREATE TABLE Customers
(
Customer_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

MySQL:
CREATE TABLE Customers
(
Customer_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Customer_Id )
)

To define a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Customers
(
Customer_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT PK_CustomerID PRIMARY KEY (Customer_Id,LastName)
)

Note: In the example above there is only ONE PRIMARY KEY (PK_CustomerID). However, the value of the PK_CustomerID is made up of two columns (Customer_Id and LastName).
It is called the Composite Primary Key.

Defining PRIMARY KEY Constraint with ALTER TABLE Statement :
To create a PRIMARY KEY constraint on the "Customer_Id" column of an existing Table, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Customers ADD PRIMARY KEY (Customer_Id)

To define a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
SQL Server / MySQL / Oracle / MS Access:
ALTER TABLE Customers ADD CONSTRAINT PK_CustomerID PRIMARY KEY (Customer_Id,LastName)

Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).

To Drop a PRIMARY KEY Constraint :
SQL Server / Oracle / MS Access:

ALTER TABLE Customers DROP CONSTRAINT pk_CustomerID

MYSQL:

ALTER TABLE Customers DROP PRIMARY KEY
-----------------------------------------------------

SQL FOREIGN KEY Constraint
A FOREIGN KEY is a key used to establish a relationship between two tables together.
A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

The Table containing the Foreign Key is called the Child table, and the table containing the candidate key is called the referenced or Parent table. The Foreign Key Column will allow the Duplicate and Null values. No default Index will be created on Foreign Key column, however, we can define if needed.

Defining SQL FOREIGN KEY with CREATE TABLE Statement
The following SQL statement creates a FOREIGN KEY on the "CustomerID" column when the "Orders" table is created:

SQL Server / Oracle / MS Access:
CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    CustomerID int FOREIGN KEY REFERENCES Customers(Customer_ID)
     )

MySQL:
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    CustomerID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID ) REFERENCES Customers(Customer_ID)
    )

To define a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    CustomerID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES                  Customers(Customer_ID)
    )

Defining a FOREIGN KEY with ALTER TABLE :
To create a FOREIGN KEY constraint on the "CustomerID" column when the "Orders" table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders ADD FOREIGN KEY (CustomerID ) REFERENCES Customers(Customer_ID);

To define a FOREIGN KEY constrain on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders ADD CONSTRAINT FK_CustomerOrder
FOREIGN KEY (CustomerID) REFERENCES Customers(Customer_ID);

To DROP a FOREIGN KEY Constraint :
To drop a FOREIGN KEY constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Orders DROP CONSTRAINT FK_CustomerOrder;

MySQL:
ALTER TABLE Orders DROP FOREIGN KEY FK_CustomerOrder;

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------