PostgreSQL - Primary Key

PostgreSQL - Primary Key

In PostgreSQL, as in other relational databases, a primary key is a column or a set of columns used to uniquely identify a row in a table. Each table can have only one primary key. The values in the primary key columns must be unique across the table, and they cannot be NULL.

Characteristics of a Primary Key:

  1. Uniqueness: Each value in the primary key must be unique. This ensures that each record in the table can be distinctly identified by its primary key value.

  2. Non-null: Primary key values cannot be NULL because they must uniquely identify records in the table.

  3. Immutability: Ideally, primary key values should not change over time. Changing primary key values can have implications if the column is used as a foreign key in other tables.

  4. Single Per Table: Each table can have only one primary key, but that primary key can consist of one or multiple columns (composite key).

Syntax:

To define a primary key while creating a table:

CREATE TABLE table_name ( column1 datatype, column2 datatype, ... PRIMARY KEY (column_name) );

For a composite primary key (multiple columns):

CREATE TABLE table_name ( column1 datatype, column2 datatype, ... PRIMARY KEY (column1, column2, ...) );

To add a primary key to an existing table:

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

Example:

  1. Creating a table with a primary key:

    CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR (50), last_name VARCHAR (50), email VARCHAR (100) );

    Here, the employee_id is the primary key, and we use the SERIAL datatype so that PostgreSQL automatically generates a unique value for this column for each new record.

  2. Creating a table with a composite primary key:

    CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, product_id) );

    In this example, both order_id and product_id together form the primary key. This means the combination of these two columns must be unique across the table.

Considerations:

  • Choosing the Primary Key: While some tables have a natural primary key (like an email address in a users table), others might not. In such cases, you might use an artificial (or surrogate) key like a generated sequence number.

  • Indexes: PostgreSQL automatically creates a unique index on the primary key column(s). This speeds up access to rows based on the primary key.

  • Foreign Key References: Primary keys often act as a reference for foreign keys in other tables, helping to maintain referential integrity in the database.

In summary, a primary key in PostgreSQL ensures that each record in a table is uniquely identifiable. Properly chosen primary keys can help maintain data integrity and improve query performance.

Examples

  1. How to define a primary key in PostgreSQL:

    • Define a primary key during table creation.
    CREATE TABLE your_table ( id SERIAL PRIMARY KEY, column1 datatype, column2 datatype );

  2. Adding a primary key constraint to an existing table in PostgreSQL:

    • Add a primary key to an existing table.
    ALTER TABLE your_table ADD PRIMARY KEY (id);

  3. Composite primary key in PostgreSQL:

    • Use multiple columns as a composite primary key.
    CREATE TABLE your_table ( id1 datatype, id2 datatype, PRIMARY KEY (id1, id2) );

  4. Using SERIAL data type with a primary key in PostgreSQL:

    • Create an auto-incrementing primary key.
    CREATE TABLE your_table ( id SERIAL PRIMARY KEY, column1 datatype );

  5. Dropping and altering primary keys in PostgreSQL:

    • Drop or alter a primary key as needed.
    ALTER TABLE your_table DROP CONSTRAINT your_table_pkey;

  6. Primary key and foreign key relationships in PostgreSQL:

    • Establish relationships between tables using primary and foreign keys.
    CREATE TABLE table1 ( id SERIAL PRIMARY KEY ); CREATE TABLE table2 ( id SERIAL PRIMARY KEY, table1_id INT REFERENCES table1(id) ); 
\d your_table 
    INSERT INTO your_table (id, column1) VALUES (1, 'value1') ON CONFLICT (id) DO NOTHING;

      More Tags

      captcha loss-function placeholder imgur word-boundary library-path error-code dependencies checkpoint view

      More Programming Guides

      Other Guides

      More Programming Examples