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:

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.
Non-null: Primary key values cannot be NULL because they must uniquely identify records in the table.
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.
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:

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.
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.

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
);

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);
```

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)
);

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
);
```
Dropping and altering primary keys in PostgreSQL:
- Drop or alter a primary key as needed.
```
ALTER TABLE your_table
DROP CONSTRAINT your_table_pkey;
```

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;