SQL Primary Key

In the big world of databases, a primary key (PK) is like a special key that helps tell each row in a table apart. This article talks about the important things to know about SQL primary keys, explaining why they're so important and how they help keep data in good shape.

What Is a Primary Key?

A primary key is a column or a set of columns designed to uniquely identify each record in a table. It is important to define a PRIMARY KEY constraint during the creation or modification of a table.

Understand the key points to remember when dealing with primary keys, including their role in enforcing entity integrity, unique data constraints, length limitations, and the prohibition of null values.

Revealing the Power of Composite Primary Keys

Discover the idea of composite primary keys, where several columns work together to create a special ID. We'll break down how to design a composite primary key with a focus on being efficient, saving space, and making your database work better.

Creating a Primary Key in SQL: A Step-by-Step Guide

SQL Primary Key for One Column

Learn the syntax for creating a primary key on a single column, whether you're working with MySQL, SQL Server, Oracle, or MS Access. Follow along with practical examples to solidify your understanding.

CREATE TABLE employee_detail
(
  emp_id numeric(10) not null,
  emp_name varchar2(50) not null,
  contac_person varchar2(50),
  CONSTRAINT constraint_name PRIMARY KEY (emp_id)
);

In this example, we've created a primary key on the employee_detail table called emp_id. It consists of only one field - the emp_id field. And also we have added the constraint name for this primary key .

SQL Primary Key for Multiple Columns

Here, we have created the primary key as the combination of two columns i.e. emp_id and emp_name because all the rows of the table employee_detail can be uniquely identified by this primary key. we can call this as a composite primary key.

CREATE TABLE employee_detail
(
  emp_id numeric(10) not null,
  emp_name varchar2(50) not null,
  contac_person varchar2(50),
  CONSTRAINT constraint_name PRIMARY KEY (emp_id,emp_name)
);

Adding a Primary Key Using ALTER TABLE

Here, You can create a primary key in Oracle using ALTER TABLE statement.

Adding Primary Key on One Column

ALTER TABLE employee_detail  
ADD PRIMARY KEY (emp_id)

Adding Primary Key on Multiple Columns

ALTER TABLE employee_detail  
ADD CONSTRAINT constraint_name PRIMARY KEY (emp_id, emp_name)

Dropping a Primary Key Constraint

You can use the ALTER statement to drop primary keys

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;