# Add Foreign Key Constraint Without A Full Lock

Adding a foreign key constraint to a large production table can cause a full table lock resulting in downtime. This is because the entire table needs to be scanned to check that the constraint is valid.

The amount of locking, and ultimately the impact on your app, can be reduced by spreading this action across two commands. First is to add the constraint without checking that all the existing records are valid.

```sql
alter table books
  add constraint fk_books_authors
  foreign key (author_id)
  references authors(id)
  not valid;
```

The constraint will be added immediately and any subsequent inserts or updates will be subject to the new foreign key constraint.

The second step is to make this constraint valid for all the existing rows.

```sql
alter table books validate constraint fk_books_authors;
```

This "validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered." This is lower impact than a full table lock.

[Source](https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-NOTES)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://ploegert.gitbook.io/til/programmy/linux/is-app-installed/indexing/add-foreign-key-constraint-without-a-full-lock.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.