SQL PRIMARY KEY
The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).
When multiple columns are used as a primary key, it is known as composite primary key.
The main advantage of this uniqueness is that we get fast access.
SQL primary key for create table:
The following SQL command creates a PRIMARY KEY on the “P_Id” column when the “students” table is created.
MySQL:
CREATE TABLE students;
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY(P_Id)
);SQL Server, Oracle, MS Access:
CREATE TABLE students;
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar (255) NOT NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255),
); SQL primary key on ALTER TABLE
When table is already created and you want to create a PRIMARY KEY constraint on the “P_Id” column you should use the following SQL:
Primary key on one column:
ALTER TABLE students;
ADD PRIMARY KEY (P_Id); To DROP a PRIMARY KEY constraint
If you want to DROP (remove) a primary key constraint, you should use following syntax:
MySQL:
ALTER TABLE students;
DROP PRIMARY KEY; SQL Server / Oracle / MS Access:
ALTER TABLE students
DROP CONSTRAINT pk_StudentID
SQL FOREIGN KEY
A foreign key in one table used to point primary key in another table.A FOREIGN KEY is a key used to link two tables together.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.
Let us take an example:
Here are two tables first one is Person table and second is orders table.
Here orders are given by students.
First table:
| P_Id | LastName | FirstName | CITY |
| 1 | Hansen | Ola | Aurangabad |
| 2 | Mishra | Raghav | Gadchiroli |
| 3 | Kumar | Sakshi | Mumbai |
Second table:
| O_Id | OrderNo | P_Id |
| 1 | 99586 | 2 |
| 2 | 46658 | 2 |
| 3 | 54846 | 3 |
| 4 | 69856 | 1 |
- In above 2 tables “PersonID” column in the “Orders” table points to the “PersonID” column in the “Persons” table.
- The “P_Id” column in the “Students” table is the PRIMARY KEY in the “Students” table.
- The “P_Id” column in the “Orders” table is a FOREIGN KEY in the “Orders” table.
- The foreign key constraint is generally prevents action that destroy links between tables.
- It also prevents invalid data to enter in foreign key column.
SQL FOREIGN KEY constraint ON CREATE TABLE:
To create a foreign key on the “P_Id” column when the “Orders” table is created:
MySQL:
CREATE TABLE orders;
(
O_Id int NOT NULL,
Order_No int NOT NULL,
P_Id int,
PRIMAY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons (P_Id)
); SQL Server /Oracle / MS Access:
CREATE TABLE Orders;
(
O_Id int NOT NULL PRIMAY KEY,
Order_No int NOT NULL,
P_Id int FOREIGN KEY REFERENCES persons (P_Id)
); SQL FOREIGN KEY constraint for ALTER TABLE:
If the Order table is already created and you want to create a FOREIGN KEY constraint on the “P_Id” column, you should write the following syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY(P_Id)
REFERENCES Students (P_Id) To dROP FOREIGN KEY COSTRAINT:
If you want to drop a FOREIGN KEY constraint, use the following syntax:
MySQL:
ALTER TABLE Orders;
ROP FOREIGN KEY fk_PerOrders; SQL Server / Oracle / MS Access:
ALTER TABLE Orders;
DROP CONSTRAINT fk_PerOrders; Unique Key in SQL
A unique key is a set of one or more than one columns of a table and it ensures that all values in a column are different.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint.
However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
SQL UNIQUE KEY constraint on CREATE TABLE:
If you want to create a UNIQUE constraint on the “P_Id” column when the “students” table is created, use the following SQL syntax:
SQL Server / Oracle / MS Access:
CREATE TABLE students
(
P_Id int NOT NULL UNIQUE,
LastName varchar (255) NOT NULL,
FirstName varchar (255),
City varchar (255)
) MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE students;
CREATE TABLE students;
(
P_Id int NOT NULL,
LastName varchar (255) NOT NULL,
FirstName varchar (255),
City varchar (255),
UNIQUE (P_Id)
); SQL UNIQUE KEY constraint on ALTER TABLE:
If you want to create a unique constraint on “P_Id” column when the table is already created, you should use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE students;
ADD UNIQUE (P_Id) ; To DROP A FOREIGN KEY constraint:
If you want to drop a UNIQUE constraint, use the following SQL syntax:
MySQL:
ALTER TABLE students;
DROP INDEX uc_studentID ; SQL Server / Oracle / MS Access:
ALTER TABLE students;
DROP CONSTRAINT uc_studentID;