How to Create Primary Key in SQL (With Syntax & FAQs)
A Primary Key is a database constraint that ensures uniqueness records in the table. It guarantees no two rows can have the same value in the primary key column(s). The Primary Key not only ensures uniqueness but also improves data access speed and is used to establish relationships between tables.
Both Primary Key and Unique Key ensure uniqueness in a table. The main differences are a Primary Key can not allow NULL value and only one primary key is allowed per table, while a Unique Key allows NULL values but requires each non-null value to be unique and multiple unique keys can exist in a table.
Syntax
Creating a Primary Key at the Time of Table Creation
CREATE TABLE TableName( column1 datatype, column2 datatype, column3 datatype, ..................... PRIMARY KEY(column(s)) );
CREATE TABLE TableName( column1 datatype PRIMARY KEY, column2 datatype, column3 datatype, ..................... );
Creating a Primary Key On an Existing Table
ALTER TABLE TableName ADD CONSTRAINT PRIMARY KEY (column(s));
Dropping Primary Key
ALTER TABLE TableName DROP PRIMARY KEY;
Example
In the following example, we are trying to create a table with name Employees in an SQL database. While creating the table, we will add the constraint "PRIMARY KEY" on the column EmployeeId.
CREATE TABLE `Employees` (
`EmployeeId` INT PRIMARY KEY ,
`Name` VARCHAR(50) NOT NULL,
`Mobile` VARCHAR(20) NOT NULL,
`JobTitle` VARCHAR(100) NOT NULL,
`Department` VARCHAR(100) NOT NULL,
`salary` DECIMAL(10, 2)
);Example (On an Existing Table)
In the following example, We are adding Primary Key on an existing table Employees on column EmployeeId.
ALTER TABLE Employees ADD CONSTRAINT PRIMARY KEY (EmployeeId);
In the following example, We are adding Primary Key on an existing table Employees on column Name and Mobile.
ALTER TABLE Employees ADD CONSTRAINT PRIMARY KEY (Name, Mobile);
Example (Dropping Primary Key)
In the following example, We are dropping Primary Key on table Employees.
ALTER TABLE Employees DROP PRIMARY KEY;
Primary Key vs Unique Key
While both Primary Key and Unique Key constraints ensure uniqueness for the column(s). Below are some key differences :-
- A table can only have one primary key.
- It cannot accept NULL values
- It automatically creates a unique index for efficient querying.
Primary Key
- A table can have multiple unique keys.
- It can accept NULL values.
- It also automatically creates a unique index but it does not enforce the "no NULL" constraint.
Unique Key
Key Points of Primary Key
- Primary key is a unique identifier for each record in a table.
- Two rows in a table can’t have the same primary key value. In other words, you can say, Every primary key value must be unique within the table.
- Primary key establish relationships between tables and ensure the integrity of the data.
- Primary key is automatically indexed by the database.
- A primary key can not contain NULL values.
- When a primary key is created on multiple columns of a table, it is called a Composite Key.
Frequently Asked Questions (FAQs)
1. What is a primary key in SQL?
A primary key is a database constraint used to uniquely identify each row in a table. It ensures that no two rows can share the same value in the primary key column or column set. A primary key also prevents NULL values, which guarantees that every record has a valid identifier. Primary keys are fundamental for data integrity and relational database design.
2. Why is a primary key important in a database?
Primary keys are essential because they enforce uniqueness and data consistency. They help the database engine quickly locate records, which improves query performance. Primary keys are also used to establish relationships between tables through foreign keys. Without a primary key, duplicate records can exist, leading to unreliable data and broken relationships.
3. Can a table have more than one primary key?
No, a table can have only one primary key. However, that single primary key can consist of multiple columns, which is known as a composite primary key. Composite keys are useful when a single column is not enough to uniquely identify a row, such as in junction or mapping tables.
4. Can a primary key contain NULL values?
No, a primary key column cannot contain NULL values. This is a strict rule enforced by the database engine. Since a primary key uniquely identifies a row, allowing NULL would break that guarantee. If a column needs to allow NULL values, it cannot be part of a primary key.
5. What is a composite primary key?
A composite primary key is a primary key made up of two or more columns. It ensures uniqueness based on the combination of values across those columns rather than a single column. Composite keys are commonly used in many-to-many relationship tables.
6. What is the difference between a primary key and a unique key?
Both primary keys and unique keys enforce uniqueness, but they are not the same. A primary key does not allow NULL values and only one primary key is allowed per table. A unique key allows NULL values and a table can have multiple unique keys. Primary keys are mainly used for identity, while unique keys enforce business rules.
7. Does a primary key automatically create an index?
Yes, when you create a primary key, the database automatically creates a unique index on that column or set of columns. This index improves query performance for searches, joins and lookups using the primary key.
8. Can I add a primary key to an existing table?
Yes, you can add a primary key to an existing table using the ALTER TABLE statement. However, the column or columns must already satisfy primary key rules, meaning no duplicate values and no NULL values.
9. Can I drop a primary key after creating it?
Yes, a primary key can be removed using ALTER TABLE DROP PRIMARY KEY. This should be done carefully because dropping a primary key may break foreign key relationships, reduce data integrity and impact application logic.
10. Can a primary key be created on text or varchar columns?
Yes, a primary key can be created on text or varchar columns as long as the values are unique and not NULL. However, large text-based primary keys can negatively impact performance, so numeric or short string keys are usually preferred.
11. Is AUTO_INCREMENT always required for a primary key?
No, AUTO_INCREMENT is not mandatory for a primary key. It is a convenience feature used when the database should generate unique values automatically. Primary key values can also be inserted manually when required.
12. Can two tables have the same primary key name?
Yes, different tables can have primary keys with the same column name, such as Id. Primary key names must be unique only within a table, not across the entire database.
13. How does a primary key help in table relationships?
Primary keys are referenced by foreign keys to create relationships between tables. This enforces referential integrity, prevents orphan records and ensures data consistency across related tables.
14. Should every table have a primary key?
Yes, almost every table should have a primary key. It enables reliable row identification, indexing, relationships and better performance. Tables without primary keys are harder to maintain and prone to data duplication.
15. What happens if I try to insert duplicate primary key values?
The database will reject the insert and throw an error. This behavior protects data integrity by ensuring that every row remains uniquely identifiable.
16. Can a primary key be used in JOIN operations?
Yes, primary keys are commonly used in JOIN operations. They provide the most efficient and reliable way to link records between tables because they are indexed and guaranteed to be unique.
17. Are primary keys commonly asked in SQL interviews?
Yes, primary keys are a very common interview topic. Interviewers often ask about primary vs unique keys, composite keys, foreign key relationships and real-world use cases.