Alter Table Add Constraint - How to Use Constraints SQL
Introduction
When working with databases you’ll need to manage the data in the database by defining the state of the specific data in a given column. There are several constraint put in data when using PostgreSQL. They include the PRIMARY KEY constraints, FOREIGN KEY constraint, CHECK constraint, UNIQUE constraint and the NOT NULL constraint. Some constraints such as the PRIMARY KEY and the FOREIGN KEY are useful when defining and managing the relationship between data in various tables in a database. In this tutorial we shall focus on the commands used to add constraints to table columns.
Basically, we use the ALTER TABLE ADD CONSTRAINT command to put specific constraint on a given table column.
The syntax to add constraints to a table column is as follows:
1 2 | ALTER TABLE TABLE_NAME ADD constaint_type (column_name); |
Let’s now look at each constraint mentioned above while giving illustrations of how you can add them.
Adding primary key constraints
To add a primary key constraint, we use the following syntax:
1 2 | ALTER TABLE tablename ADD PRIMARY KEY (column_name); |
Let’s start by creating a books table with book_id and the book_name columns using the following statement:
1 2 3 4 | CREATE TABLE books ( book_id SERIAL, book_name VARCHAR ); |
Let’s display the table structure and view it before we alter any state:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | articles=# \d books; Table "public.books" Column | Type | Collation | Nullable | Default -----------+-------------------+-----------+----------+---------------------------------------- book_id | integer | | not null | nextval('books_book_id_seq'::regclass) book_name | character varying | | | Then, we will add the primary key constraint to the book_id column as shown below: ALTER TABLE books ADD PRIMARY KEY (book_id); The following changes appear after executing the above command: articles=# \d books; Table "public.books" Column | Type | Collation | Nullable | Default -----------+-------------------+-----------+----------+---------------------------------------- book_id | integer | | not null | nextval('books_book_id_seq'::regclass) book_name | character varying | | | Indexes: "books_pkey" PRIMARY KEY, btree (book_id) |
Adding FOREIGN KEY constraint
Foreign key refers to a field or a set of fields in a table that uniquely identifies another row in another table. Normally, a foreign key in one table points to a primary key on the other table.
Foreign keys are added into an existing table using the ALTER TABLE statement. The following syntax is used:
1 2 | ALTER TABLE child_table ADD CONSTRAINT constraint_name FOREIGN KEY (c1) REFERENCES parent_table (p1); |
In the above syntax, the child_table is the table that will contain the foreign key while the parent table shall have the primary keys. C1 and p1 are the columns from the child_table and the parent_table columns respectively. Let us now add foreign key to an existing table in an example:
1 2 3 4 5 6 7 8 9 10 11 12 13 | ALTER TABLE books ADD FOREIGN KEY (book_id) REFERENCES library (book_id); The result is as shown below: articles=# \d books; Table "public.books" Column | Type | Collation | Nullable | Default -----------+-------------------+-----------+----------+---------------------------------------- book_id | integer | | not null | nextval('books_book_id_seq'::regclass) book_name | character varying | | | Indexes: "books_pkey" PRIMARY KEY, btree (book_id) Foreign-key constraints: "books_book_id_fkey" FOREIGN KEY (book_id) REFERENCES library |
ADD CHECK CONSTRAINT
CHECK constraint check whether the data in the column has met a given specification. The following syntax is used to add check constraint:
1 2 | ALTER TABLE TABLE _name ADD CONSTRAINT constaint_name CHECK (CONSTRAINT); |
Example
1 2 | ALTER TABLE student ADD CONSTRAINT age_constraint CHECK (age >= 10); |
The result of executing the above command is as show below:
1 2 3 4 5 6 7 8 9 10 11 12 13 | articles=# \d student; Table "public.student" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+------------------------------------- id | integer | | not null | nextval('student_id_seq'::regclass) name | character varying(50) | | not null | age | integer | | | gender | character varying(50) | | not null | marks | double precision | | | Indexes: "student_pkey" PRIMARY KEY, btree (id) Check constraints: "age_constraint" CHECK (age >= 10) |
ADD UNIQUE constraints
UNIQUE constraint is used when we want to ensure that the data such as email or username is unique across all the table rows. We use the syntax below to add a UNIQUE constraint to a given column:
1 2 | ALTER TABLE TABLE_NAME ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); |
Example
1 2 | ALTER TABLE teacher_details ADD CONSTRAINT email_unique UNIQUE (email); |
Below are the results of adding unique constraint in our teacher_details table:
1 2 3 4 5 6 7 8 9 10 11 12 13 | articles=# \d teacher_details; Table "public.teacher_details" Column | Type | Collation | Nullable | Default ---------------------+-------------------+-----------+----------+----------------------------------------------------- teacher_id | integer | | not null | nextval('teacher_details_teacher_id_seq'::regclass) teacher_name | character varying | | not null | registration_number | character varying | | | email | character varying | | | class_id | character varying | | | phone_number | character varying | | | Indexes: "teacher_details_pkey" PRIMARY KEY, btree (teacher_id) "email_unique" UNIQUE CONSTRAINT, btree (email) |
NOT NULL Constraint
NOT NULL constaint defines a column that should not accept null. Null means that the field has no value in it. It is however not equal to empty string or even zero. We may have encountered this type of constraint in our previous commands. The syntax to set NOT NULL constraint on an existing table column is as given below:
1 2 | ALTER TABLE TABLE_NAME ALTER COLUMN column_name SET NOT NULL; |
Example
1 2 | ALTER TABLE teacher_details ALTER COLUMN email SET NOT NULL; |
The result will be as shown in the image below:
Conclusion
In this tutorial, we have covered a set of database constraints and their usage.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started