ORACLE SQL PLSQL Primary Key Training Institutes in Kukatpally KPHB Hyderabad

SQL PRIMARY KEY

A column or columns is called primary key (PK) that uniquely identifies each row in the table.

If you want to create a primary key, you should define a PRIMARY KEY constraint when you create or modify a table.

When multiple columns are used as a primary key, it is known as composite primary key.

In designing the composite primary key, you should use as few columns as possible. It is good for storage and performance both, the more columns you use for primary key the more storage space you require.

Current Time 7:04
Duration 18:10

Loaded: 44.76%

Advertisement
Advertisement

Inn terms of performance, less data means the database can process faster.

Points to remember for primary key:

  • Primary key enforces the entity integrity of the table.
  • Primary key always has unique data.
  • A primary key length cannot be exceeded than 900 bytes.
  • A primary key cannot have null value.
  • There can be no duplicate value for a primary key.
  • A table can contain only one primary key constraint.

When we specify a primary key constraint for a table, database engine automatically creates a unique index for the primary key column.

Main advantage of primary key:

The main advantage of this uniqueness is that we get fast access.

In oracle, it is not allowed for a primary key to contain more than 32 columns.

SQL primary key for one column:

The following SQL command creates a PRIMARY KEY on the "S_Id" column when the "students" table is created.

MySQL:

  1. CREATE TABLE students
  2. (
  3. S_Id int NOT NULL,
  4. LastName varchar (255) NOT NULL,
  5. FirstName varchar (255),
  6. Address varchar (255),
  7. City varchar (255),
  8. PRIMARY KEY (S_Id)
  9. )

SQL Server, Oracle, MS Access:

  1. CREATE TABLE students
  2. (
  3. S_Id int NOT NULL PRIMARY KEY,
  4. LastName varchar (255) NOT NULL,
  5. FirstName varchar (255),
  6. Address varchar (255),
  7. City varchar (255),
  8. )

SQL primary key for multiple columns:

MySQL, SQL Server, Oracle, MS Access:

  1. CREATE TABLE students
  2. (
  3. S_Id int NOT NULL,
  4. LastName varchar (255) NOT NULL,
  5. FirstName varchar (255),
  6. Address varchar (255),
  7. City varchar (255),
  8. CONSTRAINT pk_StudentID PRIMARY KEY (S_Id, LastName)
  9. )

Note:you should note that in the above example there is only one PRIMARY KEY (pk_StudentID). However it is made up of two columns (S_Id and LastName).

SQL primary key on ALTER TABLE

When table is already created and you want to create a PRIMARY KEY constraint on the "S_Id" column you should use the following SQL:

Primary key on one column:

  1. ALTER TABLE students
  2. ADD PRIMARY KEY (S_Id)

Primary key on multiple column:

  1. ALTER TABLE students
  2. ADD CONSTRAINT pk_StudentID PRIMARY KEY (S_Id,LastName)

When you use ALTER TABLE statement to add a primary key, the primary key columns must not contain NULL values (when the table was first created).

How to DROP a PRIMARY KEY constraint?

If you want to DROP (remove) a primary key constraint, you should use following syntax:

MySQL:

  1. ALTER TABLE students
  2. DROP PRIMARY KEY

SQL Server / Oracle / MS Access:

SQL FOREIGN KEY

In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables.

In simple words you can say that, a foreign key in one table used to point primary key in another table.

Let us take an example to explain it:

Here are two tables first one is students table and second is orders table.

Advertisement
Advertisement

Here orders are given by students.

First table:

S_Id LastName FirstName CITY
1 MAURYA AJEET ALLAHABAD
2 JAISWAL RATAN GHAZIABAD
3 ARORA SAUMYA MODINAGAR

Second table:

O_Id OrderNo S_Id
1 99586465 2
2 78466588 2
3 22354846 3
4 57698656 1

Here you see that "S_Id" column in the "Orders" table points to the "S_Id" column in "Students" table.

  • The "S_Id" column in the "Students" table is the PRIMARY KEY in the "Students" table.
  • The "S_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:

(Defining a foreign key constraint on single column)

To create a foreign key on the "S_Id" column when the "Orders" table is created:

MySQL:

  1. CREATE TABLE orders
  2. (
  3. O_Id int NOT NULL,
  4. Order_No  int NOT NULL,
  5. S_Id int,
  6. PRIMAY KEY (O_Id),
  7. FOREIGN KEY (S_Id) REFERENCES Persons (S_Id)
  8. )

SQL Server /Oracle / MS Access:

  1. CREATE TABLE Orders
  2. (
  3. O_Id int NOT NULL PRIMAY KEY,
  4. Order_No int NOT NULL,
  5. S_Id int FOREIGN KEY REFERENCES persons (S_Id)
  6. )

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 "S_Id" column, you should write the following syntax:

Defining a foreign key constraint on single column:

MySQL / SQL Server / Oracle / MS Access:

  1. ALTER TABLE Orders
  2. ADD CONSTRAINT fk_PerOrders
  3. FOREIGN KEY(S_Id)
  4. REFERENCES Students (S_Id)

DROP SYNTAX for FOREIGN KEY COSTRAINT:

If you want to drop a FOREIGN KEY constraint, use the following syntax:

MySQL:

  1. ALTER TABLE Orders
  2. ROP FOREIGN KEY fk_PerOrders

SQL Server / Oracle / MS Access:

  1. ALTER TABLE Orders
  2. DROP CONSTRAINT fk_PerOrders

Difference between primary key and foreign key in SQL:

These are some important difference between primary key and foreign key in SQL-

Primary key cannot be null on the other hand foreign key can be null.

Primary key is always unique while foreign key can be duplicated.

Primary key uniquely identify a record in a table while foreign key is a field in a table that is primary key in another table.

There is only one primary key in the table on the other hand we can have more than one foreign key in the table.

By default primary key adds a clustered index on the other hand foreign key does not automatically create an index, clustered or non-clustered. You must manually create an index for foreign key.

SQL Composite Key

A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it taken individually it does not guarantee uniqueness.

Sometimes more than one attributes are needed to uniquely identify an entity. A primary key that is made by the combination of more than one attribute is known as a composite key.

In other words we can say that:

Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.

 

 

 

Advertisement

Columns that make up the composite key can be of different data types.

SQL Syntax to specify composite key:

  1. CREATE TABLE TABLE_NAME
  2. (COLUMN_1, DATA_TYPE_1,
  3. COLUMN_2, DATA_TYPE_2,
  4. ???
  5. PRIMARY KEY (COLUMN_1, COLUMN_2, ...));

In all cases composite key created consist of COLUMN1 and COLUMN2.

MySQL:

  1. CREATE TABLE SAMPLE_TABLE
  2. (COL1 integer,
  3. COL2 varchar(30),
  4. COL3 varchar(50),
  5. PRIMARY KEY (COL1, COL2));

MySQL:

  1. CREATE TABLE SAMPLE_TABLE
  2. (COL1 integer,
  3. COL2 varchar(30),
  4. COL3 varchar(50),
  5. PRIMARY KEY (COL1, COL2));

Oracle:

  1. CREATE TABLE SAMPLE_TABLE
  2. CREATE TABLE SAMPLE_TABLE
  3. (COL1 integer,
  4. COL2 varchar(30),
  5. COL3 varchar(50),
  6. PRIMARY KEY (COL1, COL2));

SQL Server:

Let's see the Syntax for the select top statement:

  1. CREATE TABLE SAMPLE_TABLE
  2. (COL1 integer,
  3. COL2 nvarchar(30),
  4. COL3 nvarchar(50),
  5. PRIMARY KEY (COL1, COL2));

Unique Key in SQL

A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table.

You can say that it is little like primary key but it can accept only one null value and it cannot have duplicate values.

The unique key and primary key both provide a guarantee for uniqueness for a column or a set of columns.

There is an automatically defined unique key constraint within a primary key constraint.

 

 

There may be many unique key constraints for one table, but only one PRIMARY KEY constraint for one table.

SQL UNIQUE KEY constraint on CREATE TABLE:

If you want to create a UNIQUE constraint on the "S_Id" column when the "students" table is created, use the following SQL syntax:

SQL Server / Oracle / MS Access:

(Defining a unique key constraint on single column):

  1. CREATE TABLE students
  2. (
  3. S_Id int NOT NULL UNIQUE,
  4. LastName varchar (255) NOT NULL,
  5. FirstName varchar (255),
  6. City varchar (255)
  7. )

MySQL:

  1. CREATE TABLE students
  2. CREATE TABLE students
  3. (
  4. S_Id int NOT NULL,
  5. LastName varchar (255) NOT NULL,
  6. FirstName varchar (255),
  7. City varchar (255),
  8. UNIQUE (S_Id)
  9. )

(Defining a unique key constraint on multiple columns):

MySQL / SQL Server / Oracle / MS Access:

  1.  CREATE TABLE students
  2. (
  3. S_Id int NOT NULL,
  4. LastName varchar (255) NOT NULL,
  5. FirstName varchar (255),
  6. City varchar (255),
  7. CONSTRAINT uc_studentId UNIQUE (S_Id, LastName)
  8. )

SQL UNIQUE KEY constraint on ALTER TABLE:

If you want to create a unique constraint on "S_Id" column when the table is already created, you should use the following SQL syntax:

(Defining a unique key constraint on single column):

MySQL / SQL Server / Oracle / MS Access:

  1. ALTER TABLE students
  2. ADD UNIQUE (S_Id)

(Defining a unique key constraint on multiple columns):

MySQL / SQL Server / Oracle / MS Access:

  1. ALTER TABLE students
  2. ADD CONSTRAINT uc_StudentId UNIQUE  (S_Id, LastName)

DROP SYNTAX FOR A FOREIGN KEY constraint:

If you want to drop a UNIQUE constraint, use the following SQL syntax:

MySQL:

  1. ALTER TABLE students
  2. DROP INDEX uc_studentID

SQL Server / Oracle / MS Access:

  1. ALTER TABLE students
  2. DROP CONSTRAINT uc_studentID

Alternate Key in SQL

Alternate key is a secondary key it can be simple to understand by an example:

Let's take an example of student it can contain NAME, ROLL NO., ID and CLASS.

Here ROLL NO. is primary key and rest of all columns like NAME, ID and CLASS are alternate keys.

If a table has more than one candidate key, one of them will become the primary key and rest of all are called alternate keys.

In simple words, you can say that any of the candidate key which is not part of primary key is called an alternate key. So when we talk about alternate key, the column may not be primary key but still it is a unique key in the column.

An alternate key is just a candidate key that has not been selected as the primary key.

How to Add Foreign Key in SQL

In this article, we will learn how to add a Foreign Key to the column in the table of our SQL database.

The FOREIGN KEY in SQL is used to join the record of two tables in the database. The column defined as the FOREIGN KEY in one table must be the PRIMARY KEY in another table in the same database.

We can easily add foreign key to the column in the following two ways:

  1. Add foreign key using Create table statement
  2. Add foreign key using Alter Table statement

If you want to add a FOREIGN KEY to the column into the SQL table, you have to follow the below steps in the given sequence:

Advertisement
  1. Create the database in the system.
  2. Create two tables in the same database.
  3. View Table structure before foreign key addition.
  4. Add a foreign key to the table.
  5. View the table structure.

Now, we are going to explain the above steps with an example:

Step 1: Create a Database

In the Structured Query Language, creating a database is the first step for storing the structured tables in the database.

Use the following SQL syntax to create a database:

  1. CREATE DATABASE Database_Name;

Suppose you want to create the Vehicles database. For this, you have to type the following command in Structured Query Language:

  1. CREATE DATABASE Vehicles;

Step 2: Create two Tables in the database

Now, you have to use the following SQL syntax for creating the tables in your database:

  1. CREATE TABLE table_name
  2. (
  3. column_Name_1 data type (size of the column_1),
  4. column_Name_2 data type (size of the column_2),
  5. column_Name_3 data type (size of the column_3),
  6. ...
  7. column_Name_N data type (size of the column_1)
  8. );

The following SQL query creates the Cars_Details table in the Vehicles database.

Advertisement
  1. CREATE TABLE Cars_Details
  2. (
  3. Car_Number INT AUTO_INCREMENT PRIMARY KEY,
  4. Model INT,
  5. Cars_Name VARCHAR (20),
  6. Colour VARCHAR (20),
  7. );

The following query creates Cars_Price_Details table in the Vehicles database:

  1. CREATE TABLE Cars_Price_Details
  2. (
  3. Model INT NOT NULL PRIMARY KEY,
  4. Cars_Price INT NOT NULL
  5. );

Step 3: View the Table Structure before Foreign key Addition

After table creation and data insertion, you can view the structure of both tables by typing the following query in your SQL application:

  1. DESC Cars
  2. or
  3. DESCRIBE Cars ;

 

Field Type NULL Key Default Extra
Car_Number INT NO PRI NULL auto_increment
Model INT Yes - NULL -
Car_Name Varchar(20) Yes - NULL
Color Varchar(20) Yes - NULL -

 

  1. DESC Cars_Price_Details;

 

Field Type NULL Key Default Extra
Car_Model INT No PRI NULL -
Car_Price INT No - NULL

Step 4: Add a Foreign key to the column in the table

If you want to add the foreign key at the time of table creation, then you have to use the following CREATE TABLE syntax in SQL:

  1. CREATE TABLE table_name1
  2. (
  3. Column_Name_1 data type (size of the column_1),
  4. Column_Name_2 data type (size of the column_2),
  5. ......,
  6. Column_Name_N data type (size of the column_N) FOREIGN KEY REFERENCES Table_Name2 (Column_Name)
  7. ) ;

Example

The following query adds the FOREIGN KEY on the 'Model' column in the Cars_Details table:

  1. CREATE TABLE Cars_Details
  2. (
  3. Car_Number INT AUTO_INCREMENT,
  4. Model INT FOREIGN KEY REFERENCES Cars_Price_Details (Car_Model),
  5. Cars_Name VARCHAR (20),
  6. Color VARCHAR (20) UNIQUE,
  7. Price INT NOT NULL
  8. ) ;

This query in SQL joins the Cars_Details table with the Cars_Price_Details table with the help of a foreign key.

Step 5: View the Table Structure after Foreign key Addition

To check the result of the query executed in the 4th step, you have to type the following DESC command in SQL:

  1. DESC Cars_Details;

 

Field Type NULL Key Default Extra
Car_Number INT Yes PRIMARY NULL auto_increment
Model INT No FOREIGN NULL -
Car_Name Varchar(20) Yes - NULL -
Color Varchar(20) Yes - NULL -
Price INT NO - NULL -
Average INT NO - 0 -

As shown in the above output, the Model column is created as the foreign key.

Add Foreign key to the Existing Table

If you want to add the foreign key to the existing table, you have to use the following ALTER syntax in SQL:

  1. ALTER TABLE Table_Name1 ADD CONSTRAINT ForeignKey_Name FOREIGN KEY (Column_Name) REFERENCES Table_Name2 (Column_Name);

The following query adds a FOREIGN KEY on the Model column when the Cars_Details table already exists in the database system:

  1. ALTER TABLE Cars_Details ADD CONSTRAINT FK_Cars_Details FOREIGN KEY (Model) REFERENCES Cars_Price_Details (Car_Model);

This ALTER query in SQL joins the Cars_Details table with the Cars_Price_Details table with the help of a foreign key.

Delete foreign key from the table

If you want to delete the foreign key from the column of the table, you have to use the following ALTER syntax in SQL:

  1. ALTER TABLE Table_Name DROP FOREIGN KEY Foreign_Key_Name;

The following query deletes the created FOREIGN KEY from the Model column of the Cars_Details table:

  1. ALTER TABLE Cars DROP FOREIGN KEY FK_Cars_Details;

How to Add a Primary Key in SQL

In this article, we will learn how to add Primary Key to the column in the table of our SQL database.

The PRIMARY KEY is used to retrieve each record of the SQL table. The field defined as the PRIMARY KEY must contain different and NOT NULL values. You can easily add a primary key to the column in the following two ways:

  1. Add Primary key using Create table statement
  2. Add primary key using Alter Table statement

If you want to add primary key to a column in the table, you have to follow the below steps in the given sequence:

  1. Create a database in the system.
  2. Create the table in the SQL database.
  3. View the table structure.
  4. Add the primary key to column in the table.
  5. View the table structure.

Now, we are going to explain the above steps with an example.

Advertisement

Step 1: Create a Database

In the structured query language, creating a database is the first step for storing the structured tables in the database.

Use the following SQL syntax to create a database:

  1. CREATE DATABASE Database_Name;

Suppose you want to create the Vehicles database. For this, you have to type the following command in Structured Query Language:

  1. CREATE DATABASE Vehicles;

Step 2: Create a Table and Insert the data

Now, you have to use the following SQL syntax to create a table in your database:

  1. CREATE TABLE table_name
  2. (
  3. column_Name_1 data type (size of the column_1),
  4. column_Name_2 data type (size of the column_2),
  5. column_Name_3 data type (size of the column_3),
  6. ...
  7. column_Name_N data type (size of the column_1)
  8. );

Suppose you want to create the Cars table in the Vehicles database. For this, you have to type the following query in your SQL application:

  1. CREATE TABLE Cars
  2. (
  3. Car_Number INT AUTO_INCREMENT,
  4. Model INT,
  5. Cars_Name VARCHAR (20),
  6. Colour VARCHAR (20),
  7. Price INT NOT NULL,
  8. ) ;

Step 3: View the Table Structure before Primary key Addition

After table creation and data insertion, you can view the structure of the Cars table by typing the following query in your SQL application:

Advertisement
  1. DESC Cars
  2. or
  3. DESCRIBE Cars ;

 

Field Type NULL Key Default Extra
Car_Number INT NO - NULL auto_increment
Model INT Yes - NULL -
Car_Name Varchar(20) Yes - NULL
Color Varchar(20) Yes - NULL -
Price INT NO - NULL -

Step 4: Add a Primary key to the column in the table

If you want to add the primary key at the time of table creation, then you have to use the following CREATE TABLE syntax in SQL:

  1. CREATE TABLE table_name
  2. (
  3. Column_Name_1 data type (size of the column_1) NOT NULL PRIMARY  KEY,
  4. Column_Name_2 data type (size of the column_2),
  5. .......,
  6. Column_Name_N data type (size of the column_N),
  7. ) ;

Example

The following query creates 'Cars' table and adds the PRIMARY KEY constraint on the 'Model' column:

  1. CREATE TABLE Cars
  2. (
  3. Car_Number INT AUTO_INCREMENT,
  4. Model INT NOT NULL PRIMARY KEY,
  5. Cars_Name VARCHAR (20),
  6. Color VARCHAR (20) UNIQUE,
  7. Price INT NOT NULL
  8. ) ;

This query in SQL does not allow database users to insert the same model of car in the Cars table.

Step 5: View the Table Structure after Primary key Addition

To check the result of the query executed in the 4th step, you have to type the following DESC command in SQL:

  1. DESC Cars;

 

Field Type NULL Key Default Extra
Car_Number INT Yes - NULL auto_increment
Model INT No PRI NULL -
Car_Name Varchar(20) Yes - NULL -
Color Varchar(20) Yes - NULL -
Price INT NO - NULL -
Average INT NO - 0 -

You can see in the above SQL output that the Model column is created as the primary key. Now, the Model column must contain unique and NOT NULL values. If you enter the same and NULL values in this column, the SQL system will show an error.

Delete Primary key from the table

If you want to delete the Primary key from the column of the table, then you have to use the following ALTER syntax in SQL:

  1. ALTER TABLE Table_Name DROP PRIMARY KEY;

The following query deletes the PRIMARY KEY from the Model column of the Cars table:

Advertisement
  1. ALTER TABLE Cars DROP PRIMARY KEY;

Add Primary key to the Existing Table

If you want to add a primary key in the existing table, you have to use the following ALTER syntax in SQL:

  1. ALTER TABLE Table_Name ADD CONSTRAINT Constraint_Name PRIMARY KEY (Column_Name);

The following query adds a PRIMARY KEY constraint on the Color column when the Cars table already exists in the database system:

  1. ALTER TABLE Cars ADD CONSTRAINT clr_prmrykey PRIMARY KEY ( Color);

This ALTER query in SQL will not allow you to insert the same color of a car in the Cars table.

Advertisement