All SQL Constraints
with Examples
SQL Constraints ensure data integrity and consistency within your
database tables, preventing invalid or inconsistent data from being
entered.
uc
by umesh chauhan
What are SQL Constraints?
SQL Constraints are rules that enforce data integrity Constraints help to maintain the accuracy and
and consistency within your database tables. These reliability of your data. They are defined at the column
rules ensure that the data inserted into the tables or table level and can be applied during the creation or
meets specific criteria and prevents invalid or modification of tables. Constraints are enforced by the
inconsistent data from being entered. database management system (DBMS), ensuring data
integrity is automatically managed.
NOT NULL Constraint
The NOT NULL constraint specifies that a column cannot have a
NULL value. This ensures that all rows in the table have a valid
value in that particular column, preventing empty or missing data.
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(255) NOT NULL,
Price DECIMAL(10,2) NOT NULL
);
UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column are unique, preventing duplicate entries. This helps to
maintain the integrity of the data and ensures that each record is distinct.
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Email VARCHAR(255) UNIQUE
);
PRIMARY KEY Constraint
The PRIMARY KEY constraint identifies a column (or combination of
columns) as the primary key for a table. This key uniquely
identifies each row in the table and is used to establish
relationships between tables.
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE
);
FOREIGN KEY Constraint
The FOREIGN KEY constraint establishes a link between two tables,
referencing the primary key of a related table. This ensures that
the values in the foreign key column correspond to existing values
in the referenced primary key column, maintaining data
consistency and integrity.
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES
Customers(CustomerID)
);
CHECK Constraint
The CHECK constraint defines a condition that the data in a column
must satisfy. This allows you to enforce specific business rules or
validation criteria to maintain the data integrity of the table.
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(255) NOT NULL,
Price DECIMAL(10,2) NOT NULL,
CHECK (Price > 0)
);
DEFAULT Constraint
The DEFAULT constraint specifies a default value for a column
when no explicit value is provided during data insertion. This helps
to ensure that all rows have a value in the column, even if the data
is not explicitly defined.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(255) NOT NULL,
LastName VARCHAR(255) NOT NULL,
Department VARCHAR(255) DEFAULT 'Sales'
);
Conclusion and Key
Takeaways
SQL Constraints are essential for maintaining data integrity and
consistency in your database tables. They enforce specific rules
and ensure the accuracy of data, making your database reliable
and efficient. Understanding and utilizing SQL Constraints is crucial
for building robust and well-structured database systems.
