SQL Constraints
Duration : 00:02:00
Constraints
Constraints are rules applied to database tables to ensure the accuracy, consistency, and integrity of data. They define restrictions on data values that can be inserted, updated, or deleted in a table. Create Constraints: To create constraints in the Server, you can use the CREATE TABLE statement with various constraint types, such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK.Example
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE
);
NOT NULL Constraint
The NOT NULL constraint ensures that a column cannot contain NULL values, meaning every row must have a valid value in that column.Example
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL
);
UNIQUE Key Constraint
The UNIQUE constraint ensures that all values in a specified column are unique across all rows in the table.Example
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100) UNIQUE,
Price DECIMAL(10, 2)
);
DEFAULT Value Constraint
The DEFAULT constraint assigns a default value to a column if no value is provided during an INSERT operation.Example
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE DEFAULT GETDATE(),
TotalAmount DECIMAL(10, 2)
);
PRIMARY Key Constraint
The PRIMARY KEY constraint uniquely identifies each row in a table and ensures that the values in the specified column(s) are unique and not NULL.Example
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);
FOREIGN Key Constraint
The FOREIGN KEY constraint establishes a relationship between two tables, ensuring that values in one table's column match values in another table's column.Example
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
CHECK Value Constraint
The CHECK constraint defines a condition that must be satisfied for data in a column. It ensures that data values meet specific criteria.Example
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Quantity INT CHECK (Quantity >= 0),
Price DECIMAL(10, 2) CHECK (Price > 0)
);
INDEX Constraint
An INDEX constraint is used to improve the retrieval speed of data from a table by creating an index on one or more columns.Example
CREATE INDEX idx_ProductName ON Products (ProductName);
Dropping Constraints
To drop constraints in the Server, you can use the ALTER TABLE statement with the DROP CONSTRAINT clause.Example
ALTER TABLE Orders
DROP CONSTRAINT FK_CustomerID;
Data Integrity Constraints
Data Integrity Constraints, including NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and INDEX constraints, play a crucial role in maintaining data accuracy and consistency within a database.Still have some questions? Let's discuss.
CONTACT US