Скачать или смотреть How to Add Unique Constraints in PostgreSQL

Learn how to enforce uniqueness on columns in PostgreSQL by adding unique constraints to your database tables. Explore the syntax and examples for creating unique constraints in PostgreSQL.
---
Disclaimer/Disclosure: Some of the content was synthetically produced using various Generative AI (artificial intelligence) tools; so, there may be inaccuracies or misleading information present in the video. Please consider this before relying on the content to make any decisions or take any actions etc. If you still have any concerns, please feel free to write them in a comment. Thank you.
---
In PostgreSQL, unique constraints are used to enforce uniqueness on a column or a group of columns in a table. This ensures that no two rows can have the same value(s) in the specified column(s). Here's how you can add unique constraints in PostgreSQL:

Adding Unique Constraints

To add a unique constraint to a column or a combination of columns in PostgreSQL, you can use the ALTER TABLE statement along with the ADD CONSTRAINT clause.

Syntax:

[[See Video to Reveal this Text or Code Snippet]]

In this syntax:

table_name is the name of the table on which you want to add the unique constraint.

constraint_name is the name you want to assign to the unique constraint (optional but recommended for managing constraints).

column_name is the name of the column or a list of columns that you want to enforce uniqueness on.

Examples

1. Adding Unique Constraint to a Single Column:

[[See Video to Reveal this Text or Code Snippet]]

This example adds a unique constraint named unique_email to the email column in the employees table. It ensures that each email address in the email column must be unique across all rows in the table.

2. Adding Unique Constraint to Multiple Columns:

[[See Video to Reveal this Text or Code Snippet]]

Here, a unique constraint named unique_customer_order is added to the combination of customer_id and order_number columns in the orders table. This constraint guarantees that each combination of customer_id and order_number is unique in the table.

Managing Unique Constraints

Once a unique constraint is added, PostgreSQL automatically ensures that the constraint is enforced for all subsequent operations (inserts, updates) on the table. If any operation violates the unique constraint, PostgreSQL will raise a constraint violation error.

You can view the existing constraints on a table using the \d table_name command in psql or by querying the information_schema tables.

Conclusion

Adding unique constraints in PostgreSQL is a powerful way to maintain data integrity by preventing duplicate entries in specific columns or combinations of columns. By understanding how to use unique constraints, you can enforce business rules and maintain data quality effectively in your PostgreSQL database.