In a table, each column is assigned a specific data type, meaning you cannot insert text into a column with an INT data type or insert a decimal number into a column with a BOOLEAN data type. These data type restrictions help prevent errors, but sometimes, they can be particular. For instance, personal ID numbers should always be unique, and customers must be adults. These specific restrictions on the values in columns are called constraints. The most common constraints are NOT NULL, UNIQUE, CHECK, DEFAULT, PRIMARY KEY, and FOREIGN KEY.
Take employees with the columns: personal_id, first_name, last_name, and age with this simple SQL query:
We need to modify this table and add some constraints to it.
In this example MySQL syntax is being used. In other SQL dialects the queries may be slightly different.
The NOT NULL constraint will not allow adding a null value to a column. In the table employees, it's possible to make the age column not null.
To add the NOT NULL constraint, use ALTER TABLE MODIFY statement:
With this SQL query, adding a new employee to the table is impossible without stating their age. If there are NULL values in the age column, it will cause an error.
To remove this constraint, use ALTER TABLE MODIFY again without the NOT NULL attribute:
It is possible to use this constraint in the CREATE TABLE statement. Just add it to the end of the column type declaration:
The UNIQUE constraint will prohibit adding duplicate values to the column. In the example, the employee identifier should be unique, so it is required to add this constraint to the personal_id column.
To add a UNIQUE constraint to the column when creating the table, specify it after column type declaration:
To add UNIQUE to an existing table with a simple query using ALTER TABLE ADD UNIQUE statement:
After the query execution, personal_id becomes unique. If duplicate values are already in this column, it will cause an error, so check the table before adding this constraint.
Sometimes it is required to make more than one column unique. In this case, define a named constraint at the end of the CREATE TABLE statement:
To drop a named constraint, use ALTER TABLE DROP INDEX statement:
Level up your tech skills and advance your career
• Wide range of SQL and Databases tracks
• Study at your own pace with your personal study plan
• Focus on practice and real-world experience