3.3 Constraints (Primary Key, Foreign Key, Unique, Check)

3.3 Constraints (Primary Key, Foreign Key, Unique, Check)

In SQL Server, constraints are rules defined on a column or a set of columns in a table to enforce data integrity. Constraints ensure that data entered into a table satisfies certain conditions. Here, we will explore four types of constraints: Primary Key, Foreign Key, Unique, and Check.

3.3.1 Primary Key Constraint

The Primary Key constraint uniquely identifies each record in a table. It ensures that the column or combination of columns it is applied to contains only unique values and does not allow NULLs.

Example:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(50)
);

In this example, the StudentID column is the primary key for the Students table.

3.3.2 Foreign Key Constraint

The Foreign Key constraint establishes a link between two tables by referencing the primary key of another table. It ensures that values in a column match the values in the referenced table's primary key.

Example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    ProductID INT,
    CONSTRAINT FK_Product FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

In this example, the ProductID column in the Orders table is a foreign key referencing the ProductID column in the Products table.

3.3.3 Unique Constraint

The Unique constraint ensures that all values in a column or a set of columns are unique. Unlike the Primary Key constraint, Unique constraints allow NULL values.

Example:

CREATE TABLE Employees (
    EmployeeID INT UNIQUE,
    EmployeeName VARCHAR(50)
);

In this example, the EmployeeID column in the Employees table has a Unique constraint.

3.3.4 Check Constraint

The Check constraint enforces a condition on a column, ensuring that values meet a specified condition or range.

Example:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    QuantityInStock INT CHECK (QuantityInStock >= 0)
);

In this example, the QuantityInStock column must have a value greater than or equal to 0.

Conclusion

Understanding and properly using constraints in SQL Server databases is crucial for maintaining data integrity. Primary Key, Foreign Key, Unique, and Check constraints collectively contribute to a well-designed and consistent database structure. As you work with SQL Server, incorporating these constraints will help ensure the accuracy and reliability of your data.