Meritshot Tutorials

  1. Home
  2. »
  3. SQL PRIMARY KEY Constraint
SQL

SQL Tutorial

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table. A primary key column (or a combination of columns) must contain unique values and cannot contain NULL values. This constraint ensures that each row in the table can be uniquely identified.

Syntax

CREATE TABLE table_name (

    column_name datatype PRIMARY KEY,

    …

);

— For composite primary key

CREATE TABLE table_name (

    column1 datatype,

    column2 datatype,

    PRIMARY KEY (column1, column2)

);

  • column_name: The name of the column designated as the primary key.
  • datatype: The type of data the column will store (e.g., INT, VARCHAR).

Example:

1 Creating a Table with a Single Primary Key

Suppose you want to create a table called Customers where the CustomerID column is the primary key:

CREATE TABLE Customers (

    CustomerID INT PRIMARY KEY,

    FullName VARCHAR(100),

    Email VARCHAR(100),

    PhoneNumber VARCHAR(15)

);

In this table, CustomerID is the primary key, ensuring each customer has a unique ID.

2 Creating a Table with a Composite Primary Key

If you want to create a table Orders where the combination of OrderID and ProductID must be unique:

CREATE TABLE Orders (

    OrderID INT,

    ProductID INT,

    OrderDate DATE,

    Quantity INT,

    PRIMARY KEY (OrderID, ProductID)

);

Here, the combination of OrderID and ProductID serves as the primary key, ensuring that each order-product pair is unique.

3 Adding a Primary Key to an Existing Table

If you have an existing table Employees and want to add a primary key constraint to the EmployeeID column:

ALTER TABLE Employees

ADD CONSTRAINT pk_EmployeeID PRIMARY KEY (EmployeeID);

This command sets the EmployeeID column as the primary key for the Employees table.

4 Handling Primary Key Violations

When inserting data into a table with a primary key constraint, ensure that the primary key value is unique. For example, if CustomerID 1 already exists:

INSERT INTO Customers (CustomerID, FullName, Email, PhoneNumber)

VALUES (1, ‘Anil Gupta’, ‘anil.gupta@example.com’, ‘9998887777’);

If you attempt to insert another row with CustomerID 1, SQL will return an error due to the violation of the primary key constraint.

Tips to Remember

  1. Ensure Uniqueness: Each value in a primary key column must be unique and non-null to maintain table integrity.
  2. Composite Keys: Use composite primary keys when a single column is not sufficient to uniquely identify rows.
  3. Indexing: Primary keys automatically create a unique index on the column(s), which improves query performance.

Frequently Asked Questions

Q1: Can a table have more than one primary key?
A1: No, a table can have only one primary key, but that primary key can consist of multiple columns (composite key).

Q2: Can a primary key column be modified after creation?
A2: Yes, you can modify the primary key column’s datatype or constraints, but you need to handle existing data carefully to ensure uniqueness.

Q3: What happens if I try to insert a duplicate value into a primary key column?
A3: SQL will return an error, preventing the insertion of a row with a duplicate primary key value.

Q4: How do I check which columns are set as primary keys?
A4: Use a DESCRIBE or SHOW INDEX command to view the primary key constraints applied to the columns.

Q5: Can I drop a primary key constraint?
A5: Yes, you can drop a primary key constraint using the ALTER TABLE command:

ALTER TABLE Customers

DROP CONSTRAINT pk_CustomerID;

This will remove the primary key constraint from the CustomerID column.

❮ Previous
Next ❯
ads website