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
1. 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.
Exemple 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)
);
Exemple 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;
2. Constraint DEFAULT
The DEFAULT constraint provides a default value for a column when the INSERT INTO statement does not provide a specific value.
Exemple 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)
);
Exemple 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;
3. Constraint UNIQUE
The UNIQUE constraint guarantees that all the values in a column cannot contain duplicates.
Exemple 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)
);
Exemple 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;
4. Constraint CHECK
The CHECK constraint allows all the values of a column to satisfy given conditions.
Exemple 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)
);
Exemple 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);
5. 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;
6. 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);
Example 3
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 4
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 ;
7. 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 1
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;
Partager ce cours avec tes amis :
