> For the complete documentation index, see [llms.txt](https://anon-coders-notes.gitbook.io/techwriterdev/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://anon-coders-notes.gitbook.io/techwriterdev/database/sql/postgresql/03_constraints.md).

# Constraints

1. **Primary Key Constraints**

* A primary key is a column or a group of columns used to identify a row uniquely in a table.
* Technically, a primary key constraint is the combination of a `not null` constraint and a `UNIQUE` constraint.
* A table can have **one and only one primary key**. It is a good practice to add a primary key to every table.
* When you add a primary key to a table, PostgreSQL creates a unique B-tree index on the column or a group of columns used to define the primary key.
* Normally, we add the primary key to a table when we define the table’s structure using CREATE TABLE statement.

```sql
    CREATE TABLE TABLE (
        column_1 data_type PRIMARY KEY,
        column_2 data_type,
        …
    );
```

* In case the primary key consists of two or more columns, you define the primary key constraint as follows:

```sql
    CREATE TABLE TABLE (
        column_1 data_type,
        column_2 data_type,
        … 
        PRIMARY KEY (column_1, column_2)
    );
```

* If you don’t specify explicitly the name for primary key constraint, PostgreSQL will assign a default name to the primary key constraint. By default, PostgreSQL uses `table-name_pkey` as the default name for the primary key constraint.
* In case you want to specify the name of the primary key constraint, you use CONSTRAINT clause as follows:

```sql
    CONSTRAINT constraint_name PRIMARY KEY(column_1, column_2,...);
```

* To add a primary key to an existing table, you can use,

```sql
    ALTER TABLE table_name ADD PRIMARY KEY (column_1, column_2);
```

* To drop a primary key constraint, you can use,

```sql
    ALTER TABLE table_name DROP CONSTRAINT primary_key_constraint;

    --Example

    ALTER TABLE products DROP CONSTRAINT products_pkey;
```

* TO add an auto incrementing by one primary key to an existing table with column name as ID, you can use,

```sql
    ALTER TABLE vendors ADD COLUMN ID SERIAL PRIMARY KEY;
```

2. **Foreign Key**

* A foreign key is a column or a group of columns in a table that reference the primary key of another table.
* The table that contains the foreign key is called the **referencing table or child table**. And the table referenced by the foreign key is called the **referenced table or parent table**.
* A table can have multiple foreign keys depending on its relationships with other tables.
* The foreign key constraint helps maintain the referential integrity of data between the child and parent tables.
* A foreign key constraint indicates that values in a column or a group of columns in the child table equal the values in a column or a group of columns of the parent table.
* Syntax

```sql
    [CONSTRAINT fk_name]
    FOREIGN KEY(fk_columns) 
    REFERENCES parent_table(parent_key_columns)
    [ON DELETE delete_action]
    [ON UPDATE update_action]
```