PostgreSQL - Create Table with Primary Key

PostgreSQL – Create Table with Primary Key

A primary key in PostgreSQL is used to uniquely identify each row in a table. It enforces two important constraints:

Uniqueness: The values in the primary key column(s) must be unique across all rows in the table.
Non-null: The primary key column(s) cannot contain NULL values.

In this tutorial, we will demonstrate how to create tables with primary keys using various scenarios, including single-column keys, composite keys, adding a primary key to an existing table, and customizing sequences for primary keys. Each example includes detailed explanations and query results for better understanding.

Example 1: Basic Table with Single Column Primary Key

Let’s start with a simple example of creating a table named students. The primary key will be a single column, student_id, which is also an auto-incrementing column using the SERIAL data type.

</>

Copy

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT
);

Insert some sample data into the table:

</>

Copy

INSERT INTO students (name, age)
VALUES 
('Alice', 20),
('Bob', 22),
('Charlie', 23);

Now, retrieve all rows from the students table:

</>

Copy

SELECT * FROM students;

Result:

Explanation: The student_id column is automatically populated with unique, sequential values (1, 2, 3) for each row inserted. These values ensure that each row can be uniquely identified. The name column stores the student names, and the age column stores their respective ages.

Example 2: Composite Primary Key

In scenarios where a single column cannot uniquely identify rows, you can define a composite primary key. Let’s create an enrollments table where a combination of student_id and course_id uniquely identifies each record.

</>

Copy

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id)
);

Insert sample data into the table:

</>

Copy

INSERT INTO enrollments (student_id, course_id)
VALUES 
(1, 101),
(1, 102),
(2, 101);

Now, retrieve all rows from the enrollments table:

</>

Copy

SELECT * FROM enrollments;

Result:

Explanation: Each row in the enrollments table is uniquely identified by the combination of student_id and course_id. This ensures that a student cannot enroll in the same course more than once. The enrollment_date column stores the date of enrollment and defaults to the current date.

Attempting to insert a duplicate combination of student_id and course_id will result in an error:

</>

Copy

INSERT INTO enrollments (student_id, course_id)
VALUES (1, 101); -- This will fail

Error Message: ERROR: duplicate key value violates unique constraint

Example 3: Adding Primary Key to an Existing Table

If you have an existing table without a primary key, you can add one using the ALTER TABLE command. Let’s consider a table named books that initially does not have a primary key.

</>

Copy

CREATE TABLE books (
    book_id INT,
    title VARCHAR(100) NOT NULL
);

-- Add a primary key constraint
ALTER TABLE books
ADD CONSTRAINT books_pk PRIMARY KEY (book_id);

Explanation: The ALTER TABLE command adds a primary key constraint to the book_id column, ensuring all values in this column are unique and non-null going forward.

Attempting to insert duplicate or null values in the book_id column after adding the primary key constraint will result in an error.

</>

Copy

INSERT INTO books (book_id, title)
VALUES (1, 'PostgreSQL Guide'), (1, 'SQL Handbook'); -- This will fail

Error Message: ERROR: duplicate key value violates unique constraint

Conclusion

Primary keys are essential for maintaining data integrity in PostgreSQL tables. They ensure that every row in the table can be uniquely identified. By exploring examples like single-column keys, composite keys, and adding keys to existing tables, you can handle a variety of use cases effectively.

TutorialKart

PostgreSQL – Create Table with Primary Key