PRIMARY KEY in SQL

Last Updated: 14th September, 2024

A SQL primary key could be a column (or combination of columns) in a table that uniquely recognizes each row. It may be a special type of constraint, and is the most imperative characteristic of any relational database table. Primary keys must contain unique values and cannot contain null values. They are used to reference a row in another table, form relationships, and enforce data integrity.

Points to Remember for the Primary Key in SQL

1. Primary keys must contain unique values.
2. Primary keys should not contain null values.
3. Primary keys should not contain duplicate values.
4. Primary keys should be short and concise.
5. Primary keys should be kept from being changed or modified.
6. Primary keys should be stable over time.
7. Primary keys should be chosen wisely.
8. Primary keys should be simple and intuitive.

Advantage of the Primary Key in SQL

The primary key's main advantage is ensuring data integrity by uniquely identifying each row in a table. This helps to prevent duplicate records from being created and ensures that each record can easily be retrieved and updated. Primary keys also help improve queries' performance as they allow for faster searching and indexing.

SQL Primary Key for One Column

Syntax

ALTER TABLE table_name
ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

This can be an SQL statement utilized to make a primary key for a single column in a table. The statement includes a column named "id" of type INT (integer) and sets it as the primary key, meaning it'll be utilized to identify each row within the table interestingly. The AUTO_INCREMENT keyword causes the column's value to increment automatically each time a new row is inserted into the table.

Example

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT
);

This statement creates a new " students " table with four columns: student_id, first_name, last_name, and age. The student_id column is set to be the PRIMARY KEY, meaning that it must contain a unique value for each row in the table.

SQL Primary Key for Multiple Columns

Syntax

CREATE TABLE table_name (
    column1 INT NOT NULL,
    column2 INT NOT NULL,
    column3 INT NOT NULL,
    PRIMARY KEY (column1, column2, column3)
);

This creates a table with three columns, all of which are integers and are not allowed to be null. It then creates a primary key constraint on all three columns, ensuring that all the values in the three columns together are unique.

Example

CREATE TABLE students (
    student_id INT NOT NULL,
    class INT NOT NULL,
    period INT NOT NULL,
    PRIMARY KEY (student_id, class, period)
);

This creates a table of students, with each row containing the student's ID, the class they are in, and the period of the day. The primary key ensures that each student is unique for a given class and period.

How to Create an SQL Primary Key?

Creating a Primary Key in PostgreSQL using pgAdmin

Step 1: Open pgAdmin and Connect to Your Database

1. Open pgAdmin and log in using your credentials.
2. On the left panel (Object Browser), navigate to your database by expanding the tree structure (Servers > [Your Server] > Databases > [Your Database]).

Step 2: Create a New Table

1. Right-click on the Tables option under your database and select Create > Table.
2. In the Create-Table window:
   • Name: Enter the table name (e.g., students).
   • Schema: Leave this as public unless you are using a custom schema.

Create Table in pgAdmin

Create Table Students

Step 3: Define Columns

1. Click on the Columns tab to add your columns.
2. Click the + (Add) button to add a new column.
   • For example:
     • Column Name: roll_no
     • Data Type: integer
     • Check the Primary Key checkbox to designate it as a primary key.
     • Not Null: Ensure this checkbox is also selected since primary keys cannot be NULL.
   • Add other columns (e.g., student_name, age, course, email).

Define Columns and set Primary Key

Step 4: Set a Primary Key (Alternative Method)

If the primary key is not set when defining the column, you can set it using Constraints.

1. Click on the Constraints tab in the same Create-Table window.
2. Click + (Add) to add a new constraint.
3. Set the following:
   • Name: Enter a name for your primary key constraint (e.g., students_pkey).
   • Type: Select Primary Key.
   • Columns: Choose the column(s) you want to set as the primary key (e.g., roll_no).
4. Click Save to create the table with the primary key.

Set Primary Key in Constraints

Step 5: Review and Execute

1. After you have added all columns and set the primary key, click Save.
2. pgAdmin will generate the SQL behind the scenes and execute it to create your table with the primary key.

Step 6: Verify the Table Creation

1. Expand the Tables section under your database to see the newly created table.
2. Right-click on the table and select View/Edit Data > All Rows to check if the table is created correctly.

Verify table Creation with Primary Key

Creating a Primary Key in PostgreSQL using Code

Use the Query Tool in pgAdmin to run SQL commands directly

Query Tool in pgAdmin

SQL Query to Create the students Table:

Inserting Data into the students Table:

Querying the students Table:

Output:

create table and pk using code

SQL PRIMARY KEY on CREATE TABLE

Creating a PRIMARY KEY in SQL can vary slightly depending on the SQL database tool or system that is being used (e.g., MySQL, PostgreSQL, SQL Server, SQLite). Below are examples of how to define a PRIMARY KEY during a CREATE TABLE statement for different SQL systems:

1. MySQL

In MySQL, you can define a PRIMARY KEY either inline (within the column definition) or after defining all columns.

Example:

2. PostgreSQL

In PostgreSQL, the syntax is similar to MySQL. You can define a PRIMARY KEY either inline with the column or as a separate constraint.

Example:

3. SQL Server (T-SQL)

In SQL Server, you define the PRIMARY KEY using the same general syntax, but you may use IDENTITY for auto-increment behavior.

Example:

4. SQLite

In SQLite, you define a PRIMARY KEY within the column definition. SQLite supports the AUTOINCREMENT keyword for primary keys.

Example:

5. Oracle

In Oracle, the PRIMARY KEY is defined either inline or after defining all the columns. Oracle also supports sequences to generate unique values instead of auto-increment.

Example:

Composite Primary Key

To create a PRIMARY KEY based on multiple columns (composite key), you define it at the end of the CREATE TABLE statement:

Each SQL dialect may have small variations, but the general pattern of defining a primary key remains similar.

SQL Primary Key on ALTER TABLE

Primary Key on One Column

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

This query is utilized to include a primary key to an existing table. The 'table_name' indicates the name of the table to which the primary key will be included. The 'constraint_name' indicates the title of the constraint to be included. The 'column_name' indicates the column's title on which the primary key will be included.

Example

ALTER TABLE orders
ADD CONSTRAINT orders_pk PRIMARY KEY (orderID);

This query is used to add a primary key to the 'orders' table. The primary key is named 'orders_pk' and is applied to the 'orderID' column. This primary key will ensure that no two orders have the same orderID.

Primary Key on Multiple Columns

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(column1, column2);

This statement adds a primary key constraint to a table and specifies multiple columns that should be part of the primary key. The constraint_name is an optional name for the constraint. The columns listed in the parentheses are the columns that will be part of the primary key.

Example

ALTER TABLE students ADD CONSTRAINT student_pk PRIMARY KEY(student_id, student_name);

This statement adds a primary key constraint named "student_pk" to the table "students" and specifies that the columns "student_id" and "student_name" should be part of the primary key. This means that each combination of values for these two columns must be unique in the table.

How to DROP a PRIMARY KEY Constraint?

To drop a primary key constraint, use the following SQL syntax:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

For example:

ALTER TABLE student_table DROP CONSTRAINT pk_student_table_id;

This SQL statement will drop the primary key constraint on the student_table, identified as pk_student_table_id. After executing this statement, the primary key constraint will no longer be enforced on the student_table.

Conclusion

A primary key in SQL may be a column or combination of columns in a table that interestingly distinguishes each row. It implements data integrity, prevents duplicate records, and permits speedier searching and indexing. An essential key must contain one-of-a-kind and non-null values and be steady over time and can be made utilizing the Alter TABLE or Create TABLE statement. It can be dropped using the ALTER TABLE statement as well.

Key Takeaways

1. A primary key is a unique identifier used to identify a record in a database table uniquely.
2. It is typically composed of one or more columns uniquely identifying a row in a table.
3. A primary key column must contain unique values and cannot contain null values.
4. Primary keys are used to enforce entity integrity as they ensure that no two rows have the same values.
5. In SQL, the primary key is specified using the PRIMARY KEY constraint.

Quiz

1.What is a primary key in SQL? 

1. A unique identifier for each row in Table 
2.  A value must be the same for each row in table 
3.  A special type of key used to join tables 
4.  A type of constraint used to ensure data integrity

Answer: a. A unique identfier for each row in a table

2.What type of data must a primary key in SQL contain? 

1. A unique value 
2. A non-unique value 
3. A string of characters 
4. A numerical value

Answer: a. A unique value

3. What is the purpose of using a primary key in SQL? 

1. To ensure data consistency 
2. To identify rows in the table 
3. To enforce business rules 
4. To improve performance

Answer: b. To identify rows in a table

4. What is the syntax for creating a primary key in SQL?

1. ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY 
2. UPDATE table_name SET PRIMARY KEY 
3. CREATE TABLE table_name PRIMARY KEY 
4. ALTER TABLE table_name PRIMARY KEY

Answer: d. ALTER TABLE table_name PRIMARY KEY