Creation of Constraints at the Time of Table Creation with SQL

Creation of Constraints at the Time of Table Creation
Constraints are essential in SQL to ensure data integrity and consistency. They define rules that the data in a table must follow. Constraints can be defined when creating a table and help maintain data accuracy by enforcing specific rules.
Types of Constraints
Here are the main types of constraints you can create when creating a table:
PRIMARY KEY
• Purpose: Ensures that each row in the table has a unique identifier and that no null values ​​are allowed in the primary key column(s).
• Syntax: 

CREATE TABLE TableName (
ColumnNameDataType PRIMARY KEY
);

• Example : 

CREATE TABLE Employees (
IDEEmploye INT PRIMARY KEY,
First name VARCHAR(50),
Name VARCHAR(50)
);

Here, IDEmploye is set as primary key, which means it must be unique and not null.
FOREIGN KEY
• Objective: Establishes a relationship between the columns of different tables. It ensures that the value in the foreign key column exists in the primary key column of another table.
• Syntax: 

CREATE TABLE TableName (
ColumnNameDataType,
ForeignKeyDataType,
FOREIGN KEY (ForeignKey) REFERENCES OtherTable (PrimaryKey)
);

• Example : 

CREATE TABLE Commands (
CommandID INT PRIMARY KEY,
IDEmployee INT,
DateDATE command,
FOREIGN KEY (IDEmploye) REFERENCES Employees (IDEmploye)
);

Here, IDEmploye in the Orders table is a foreign key that refers to IDEmploye in the Employees table.

UNIQUE (Unique)
• Purpose: Ensures that all values ​​in a column or group of columns are unique in the table, but allows null values.
• Syntax: 

CREATE TABLE TableName (
ColumnName UNIQUE DataType
);

• Example : 

CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100) UNIQUE
);

In this example, ProductName must be unique in the Products table.
NOT NULL
• Purpose: Ensures that a column cannot have a null value, meaning it must always contain a value.
• Syntax: 

CREATE TABLE TableName (
ColumnNameDataType NOT NULL
);

• Example : 

CREATE TABLE Customers (
ClientID INT PRIMARY KEY,
CustomerName VARCHAR(100) NOT NULL
);

Here, ClientName cannot be null; each row must have a value for that column.
CHECK
• Purpose: Ensures that all values ​​in a column satisfy a specific condition. It allows you to apply domain constraints.
• Syntax: 

CREATE TABLE TableName (
ColumnNameDataType,
CHECK (condition)
);

• Example : 

CREATE TABLE Employees (
IDEEmploye INT PRIMARY KEY,
Salary DECIMAL(10, 2),
CHECK (Salary >= 0)
);

This CHECK constraint ensures that Salary cannot be negative.
DEFAULT (Default Value)
• Purpose: Provides a default value for a column when no value is specified during an insert operation.
• Syntax: 

CREATE TABLE TableName (
ColumnNameDataType DEFAULTDefaultValue
);

• Example : 

CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
QuantityStock INT DEFAULT 0
);

Here, StockQuantity will default to 0 if no value is provided.

Syntax of Constraints when Creating Tables
When you create a table, you can include constraints directly in the CREATE TABLE statement. Constraints can be set for individual columns or for the entire table.
Column Level Constraints
• Definition: Constraints are applied directly to a column.
• Syntax: 

CREATE TABLE TableName (
ColumnName DataType Constraint,
...
);

• Example : 

CREATE TABLE Employees (
IDEEmploye INT PRIMARY KEY,
First name VARCHAR(50) NOT NULL,
Name VARCHAR(50),
Email VARCHAR(100) UNIQUE
);

Here, IDEmploye is the primary key, First Name cannot be null, and Email must be unique.
Table Level Constraints
• Definition: Constraints are applied to one or more columns and can be used for constraints across multiple columns, such as composite keys or uniqueness constraints.
• Syntax: 

CREATE TABLE TableName (
Column1 DataType,
Column2 DataType,
...
CONSTRAINT ConstraintName ConstraintType (Column1, Column2, ...)
);

• Example : 

CREATE TABLE Commands (
CommandID INT,
ProductID INT,
DateDATE command,
PRIMARY KEY (OrderID, ProductID),
FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
);

In this example, a composite primary key is defined for OrderID and ProductID, and ProductID is a foreign key referencing the Products table.
Advantages of Constraints
• Data Integrity: Constraints ensure that the data stored in the table follows certain rules, which helps maintain accuracy and consistency.
• Error Prevention: They prevent the insertion of invalid data, reducing the risk of errors and ensuring that business rules are applied.
• Data Validation: Constraints provide built-in data validation, which can simplify application logic and reduce the need for additional validation code.

Conclusion
Constraints are a fundamental part of table design in SQL. They help enforce rules and maintain data integrity by specifying conditions that data must meet. By defining constraints at table creation time, you ensure that your data adheres to the required rules, supporting consistent and accurate data management.