Definition: The NOT NULL constraint ensures that a column cannot have a NULL value. It is used to enforce a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
Example:
CREATE TABLE Employees (
EmployeeID int NOT NULL,
FirstName varchar(100) NOT NULL,
LastName varchar(100) NOT NULL
);Explanation: In this table, EmployeeID, FirstName, and LastName must always have a value and cannot be left blank.
Definition: The UNIQUE constraint ensures that all values in a column are different. This constraint prevents duplicate entries in the column that is not the primary key.
Example:
CREATE TABLE Members (
MemberID int NOT NULL,
Email varchar(255) UNIQUE
);Explanation: This ensures that no two members can have the same email address, maintaining uniqueness across the Email column.
Definition: The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain unique values, and cannot contain NULL values. A table can have only one primary key, which may consist of single or multiple fields.
Example:
CREATE TABLE Orders (
OrderID int PRIMARY KEY,
OrderDate date NOT NULL,
MemberID int,
Amount decimal NOT NULL
);Explanation: OrderID serves as the primary key and uniquely identifies each order in the Orders table.
Definition: The FOREIGN KEY constraint is a key used to link two tables together. A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Example:
CREATE TABLE Payments (
PaymentID int PRIMARY KEY,
OrderID int,
PaymentDate date,
Amount decimal,
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);Explanation: This defines OrderID as a foreign key in the Payments table that references the OrderID primary key in the Orders table, establishing a relationship between the two tables.
Definition: The CHECK constraint is used to limit the range of values that can be placed in a column. If you define a CHECK constraint on a column it will allow only certain values for this column.
Example:
CREATE TABLE Products (
ProductID int PRIMARY KEY,
Price decimal CHECK (Price > 0)
);Explanation: This ensures that the Price must be greater than 0, preventing negative prices or zero from being entered.
These constraints are integral to ensuring data integrity in SQL databases. They enforce rules that help maintain accurate, reliable, and consistent data across relational databases. Whether you’re enforcing uniqueness, establishing relationships between tables, or validating data entry, constraints are essential tools in database management and design.