SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

SQL Primary Key

A field, which contains unique data in a table, is called a PRIMARY KEY (PK). It means, a PRIMARY KEY field must contain unique data in the table.

  • A PRIMARY KEY field contains values on each row.
  • PRIMARY KEY field cannot contain an empty string.
  • PRIMARY KEY data is never changed over time.
  • A table can consist of only one PRIMARY KEY, which may consist of single or multiple fields in the table. When more than one column is used as the table's PRIMARY KEY, they are known as the Composite key in the table.
  • There will be no duplicated records for a PRIMARY KEY.
  • If a field is set as a PRIMARY KEY in the table, that field cannot contain the values of the same field more than once in the table.

For example, we have a Student table in the database. The table contains 4 fields Student_Id, Student_Name, Phone_Number, and Email. Student_Id and Email is the PRIMARY KEY in the Student table. There is only one PRIMARY KEY but can have single or multiple fields in the table, as we told above. One student's data is already present in the table, but the same student adds his data again with the same email ID. As the student enters details, a pop-up will display a message “Email has already existed”. It is shown because Email has already existed in the table, and the email field is the PRIMARY KEY, and the PRIMARY KEY cannot contain duplicate values.

Create PRIMARY KEY on SQL TABLE

The following SQL query creates a PRIMARY KEY on the Employee_Id field when the ‘Employee’ table is created.

CREATE TABLE Employee( Employee_Id int PRIMARY KEY, Employee_Name varchar(40) NOT NULL, Salary int NOT NULL, Department varchar(40), City varchar(40) );        

The above query has defined PRIMARY KEY to the Employee_Id field. We can mention the key name after the field name and data type. The above query is the one way we can create Employee_Id as a PRIMARY KEY.

To check whether our PRIMARY KEY is successfully defined or not, we can use the DESC command followed by the Employee table name.

DESC Employee;

FieldsTypeNullKeyDefaultExtra
Employee_IdInt(11)NOPRINULL 
Employee_NameVarchar(40)NO NULL 
SalaryInt(11)NO NULL 
DepartmentVarchar(40)YES NULL 
CityVarchar(40)YES NULL 
SQL Primary Key

The Key field PRI is mentioned in front of the Employee_Id row, which means Employee_Id is successfully defined as the PRIMARY KEY.

Advantages of using PRIMARY KEY in the table:

  1. Fast Access to the data from the table.
  2. Duplicates values are not allowed in the PRIMARY KEY fields.

Another way to create a PRIMARY KEY

CREATE TABLE Employee( Employee_Id int NOT NULL, Employee_Name varchar(40) NOT NULL, Salary int NOT NULL, Department varchar(40), City varchar(40), PRIMARY KEY(Employee_ID) );

In the same query as above, we have to mention NOT NULL in front of the field name, which is created as the PRIMARY KEY, and at the end, we have to write the PRIMARY KEY keyword and Field name in parenthesis just like the above query.

Create a PRIMARY KEY constraint on the fields when the table already exists

Use the following query:

ALTER TABLE Employee ADD PRIMARY KEY(Employee_Id);

Using the ALTER TABLE query to define PRIMARY KEY, the PRIMARY KEY field has already been declared with NOT NULL constraint.

SQL PRIMARY KEY on Multiple Columns

The following SQL query creates a PRIMARY KEY on more than one column when the 'Student' Table is created.

CREATE TABLE Student( Student_Id int NOT NULL, Student_Name varchar(40), Department varchar(40), Phone_Number int(10), Email varchar(100) NOT NULL, PRIMARY KEY(Student_ID, Email));

Only one PRIMARY KEY is made in the example on Student_Id and Email.

To check whether our PRIMARY KEY is successfully defined or not, we can use the DESC command followed by the Student table name.

DESC Student;
FieldsTypeNullKeyDefaultExtra
Student_IdInt(11)NOPRINULL 
Student_NameVarchar(40)YES NULL 
DepartmentVarchar(40)YES NULL 
Phone_NumberInt(10)YES NULL 
EmailVarchar(100)NOPRINULL 

As we can see in front of the Student_Id and Email row, Key fields PRI is mentioned, which means Student_Id and Email are successfully defined as PRIMARY KEY.

SQL Primary Key

We will now execute SHOW CREATE TABLE followed by Student table name, which will describe the student table.

SHOW CREATE TABLE Student;
SQL Primary Key

In the above output, we can see that only one PRIMARY KEY is created, and in parenthesis, the fields name is mentioned, which is created as a PRIMARY KEY while creating the table.

Suppose we want to remove the PRIMARY KEY constraint from the student table. We will use the following query to drop the PRIMARY KEY constraint:

ALTER TABLE Student DROP PRIMARY KEY;

To check whether our PRIMARY KEY is successfully removed or not, we will use the DESC command followed by the Student table name.

DESC Student;
FieldsTypeNullKeyDefaultExtra
Student_IdInt(11)NO NULL 
Student_NameVarchar(40)YES NULL 
DepartmentVarchar(40)YES NULL 
Phone_NumberInt(10)YES NULL 
EmailVarchar(100)NO NULL 
SQL Primary Key



ADVERTISEMENT
ADVERTISEMENT