Constraints in SQL

Constraints in SQL

Constraints allow you to limit the type of data to insert into a table. These constraints can be specified when creating the table using the CREATE TABLE statement, or after creating the table using the ALTER TABLE statement.

Constraints can be at the column or table level. Column level constraints are applied to only one column, while table level constraints apply to the entire table.

The most common constraints are :

  •  NOT NULL
  •  DEFAULT
  •  UNIQUE
  •  CHECK
  •  PRIMARY KEY
  •  FOREIGN Key
  •  INDEX

Constraint NOT NULL

A column accepts a NULL value by default. To reject a NULL value in a column, you must define a constraint on this column indicating that it does not accept a NULL value.

NULL is not the same thing as no data, rather it represents unknown data.

Example 1 :

For example, the following SQL query creates a new table called Employees and adds four columns, three of which are Id, Name and Age, we specify to not accept NULL values.

                                CREATE TABLE Employees(
                                    Id INT              NOT NULL,
                                    Name VARCHAR (20)    NOT NULL,
                                    Age  INT            NOT NULL,
                                    Salary   DECIMAL (18, 2),       
                                    PRIMARY KEY (Id)
                                );
                            
Example 2 :

If the Employees table has already been created, to add a NOT NULL constraint to the Salary column in MySQL, you must write a query as follows:

                                ALTER TABLE Employees
                                    MODIFY Salary DECIMAL (18, 2) NOT NULL;
                            

Constraint DEFAULT

The DEFAULT constraint provides a default value for a column when the INSERT INTO statement does not provide a specific value.

Example 1 :

For example, the following SQL code creates the same Employees table, but here the Salary column is set to 5000.00 by default.

                                CREATE TABLE Employees(
                                    Id INT              NOT NULL,
                                    Name VARCHAR (20)    NOT NULL,
                                    Age  INT            NOT NULL,
                                    Salary   DECIMAL (18, 2) DEFAULT 5000.00,       
                                    PRIMARY KEY (Id)
                                );
                            
Example 2 :

If the Employees table has already been created, to add a DEFAULT constraint to the Salary column in MySQL, you must write a query as follows :

                                ALTER TABLE Employees
                                    MODIFY Salary  DECIMAL (18, 2) DEFAULT 5000.00;
                            

To remove a DEFAULT constraint, use the following query:

                                ALTER TABLE Employees
                                    ALTER COLUMN Salary DROP DEFAULT;
                            

Constraint UNIQUE

The UNIQUE constraint guarantees that all the values in a column cannot contain duplicates.

Example 1 :

For example, the following SQL query creates the same Employees table, but in this case, the Name column is set to UNIQUE, so that you cannot have two records with the same Name.

                                CREATE TABLE Employees(
                                    Id INT              NOT NULL,
                                    Name VARCHAR (20)    NOT NULL UNIQUE,
                                    Age  INT            NOT NULL,
                                    Salary   DECIMAL (18, 2),       
                                    PRIMARY KEY (Id)
                                );
                            
Example 2 :

If the Employees table has already been created, to add a UNIQUE constraint to the Name column in MySQL, you must write a query as follows :

                                ALTER TABLE Employees
                                    MODIFY Name VARCHAR(20) NOT NULL UNIQUE;
                            

Constraint CHECK

The CHECK constraint allows all the values of a column to satisfy given conditions.

Example 1 :

For example, the following SQL query creates the same Employees table, but in this case, the Age column is set to CHECK, so that you cannot have an employee under the age of 18.

                                CREATE TABLE Employees(
                                    Id INT              NOT NULL,
                                    Name VARCHAR (20)    NOT NULL,
                                    Age  INT            NOT NULL CHECK (Age >= 18),
                                    Salary   DECIMAL (18, 2),       
                                    PRIMARY KEY (Id)
                                );
                            
Example 2 :

If the Employees table has already been created, to add a CHECK constraint to the Age column in MySQL, you must write a query as follows:

                                ALTER TABLE Employees
                                    MODIFY Age INT NOT NULL CHECK (Age >= 18);
                            

Constraint INDEX

The INDEX is used to create and retrieve data from the database very quickly. An index can be created using a single column or a group of columns in a table. When the index is created, a ROWID is assigned to each row before the data is sorted.

Appropriate indexes are good for performance in large databases, but you should be careful when creating an index. A selection of fields depends on what you use in your SQL queries.

You can create an index on one or more columns using the syntax given below.

                                CREATE INDEX index_name
                                    ON table_name ( column1, column2, ...);
                            

To create an index on the Age column, to optimize the search for employees for a specific age, you can use the following syntax:

                                CREATE INDEX idx_age
                                    ON Employees (Age);
                            

To remove an INDEX constraint, use the following SQL syntax.

                                ALTER TABLE Employees
                                    DROP INDEX idx_age;
                            

Constraint PRIMARY KEY

The primary key helps identify only each row in a table. It can represent part of a concrete record, or be an artificial field (a field that has nothing to do with the actual record). The primary key can represent one or more fields of a table. When the primary key represents several fields, it is called "composite key".

It is possible to specify the primary keys when creating the table (using CREATE TABLE) or modifying the structure of the existing table (using ALTER TABLE).

Example 1 :

Here is the syntax to set the Id attribute as a primary key in an Employees table.

                                CREATE TABLE Employees(
                                    Id INT              NOT NULL,
                                    Name VARCHAR (20)    NOT NULL,
                                    Age  INT            NOT NULL,
                                    Salaire   DECIMAL (18, 2),       
                                    PRIMARY KEY (Id)
                                );
                            
Example 2 :

To create a PRIMARY KEY constraint on the "Id" column when the Employees table already exists, use the following SQL syntax:

                                ALTER TABLE Employees
                                    ADD PRIMARY KEY (ID);
                            
Warning ! If you use the ALTER TABLE statement to add a primary key, the primary key columns must already have been declared as not containing NULL values (when the table was created).
Example 1 :

To define a PRIMARY KEY constraint on several columns, use the SQL syntax given below.

                                CREATE TABLE Employees(
                                    Id INT              NOT NULL,
                                    Name VARCHAR (20)    NOT NULL,
                                    Age  INT            NOT NULL,
                                    Salary   DECIMAL (18, 2),       
                                    PRIMARY KEY (Id, Nom)
                                );
                            
Example 2 :

To create a PRIMARY KEY constraint on the "Id" and "Name" columns when the Employees table already exists, use the following SQL syntax:

                                ALTER TABLE Employes
                                    ADD PRIMARY KEY (Id, Name);
                            

You can remove the primary key constraints from the table with the syntax given below.

                                ALTER TABLE Employees DROP PRIMARY KEY ;
                            

Constraint FOREIGN KEY

The Foreign key represents a field (or fields) which points to the primary key of another table. The purpose of the foreign key is to ensure the referential integrity of the data. In other words, only values that should appear in the database are allowed.

A foreign key is a column or a combination of columns whose values correspond to a primary key in another table.

Example 2 :

Consider the structure of the following two tables.

                                CREATE TABLE Employees(
                                    Id INT              NOT NULL,
                                    Name VARCHAR (20)    NOT NULL,
                                    Age  INT            NOT NULL,
                                    Salary   DECIMAL (18, 2),       
                                    PRIMARY KEY (Id, Nom)
                                );
                            
                                CREATE TABLE Holiday(
                                    Id INT              NOT NULL,
                                    start_date  DATE,
                                    end_date  DATE,
                                    ID_EMP  INT REFERENCES Employees(Id),    
                                    PRIMARY KEY (Id)
                                );
                            

Or

                                CREATE TABLE Holiday(
                                    Id INT              NOT NULL,
                                    start_date  DATE    NOT NULL,
                                    end_date  DATE      NOT NULL,
                                    ID_EMP  INT,    
                                    PRIMARY KEY (Id),
                                    FOREIGN KEY (ID_EMP) REFERENCES Employees(Id)
                                );
                            
Example 2 :

If the Holiday table has already been created and the foreign key has not yet been defined, use the following syntax to specify a foreign key by modifying a table.

                                ALTER TABLE Holiday
                                    ADD FOREIGN KEY (ID_EMP) REFERENCES Employees(Id);
                            

To remove a FOREIGN KEY constraint, use the following syntax:

                                ALTER TABLE Holiday
                                    DROP FOREIGN KEY;
                            

Removal of constraints

Any constraint that you have defined can be removed using the ALTER TABLE command with the DROP CONSTRAINT option.

Syntax :
                                ALTER TABLE table_name DROP CONSTRAINT constraint_name;
                            

Share this course with your friends :

This course is written by M. ESSADDOUKI Mostafa

Many people realize their hearts desires late in life. Continue learning, never stop striving and keep your curiosity sharp, and you will never become too old to appreciate life.

0 Comment(s)

To leave a comment you must have an account Sign up, or Sign in