Constraints (Primary Key, Foreign Key, Unique, Not Null, Check, Default) in SQL

Constraints in SQL are rules that are applied to columns in a table to enforce data integrity and ensure that data entered into the database adheres to specific standards. SQL provides several types of constraints, such as Primary Key, Foreign Key, Unique, Not Null, Check, and Default, each of which serves a different purpose.

1. Primary Key Constraint

The Primary Key constraint uniquely identifies each record in a table. A table can have only one Primary Key, which may consist of one or more columns. The values in a Primary Key column must be unique and cannot be NULL.

Example: Creating a Table with a Primary Key

      -- Create the 'Students' table with 'StudentID' as the Primary Key
      CREATE TABLE Students (
        StudentID INT PRIMARY KEY,
        Name VARCHAR(100),
        Age INT
      );
    

In this example, 'StudentID' is the Primary Key. Each student will have a unique 'StudentID', and no two students can have the same 'StudentID'.

2. Foreign Key Constraint

The Foreign Key constraint is used to ensure referential integrity between two tables. It establishes a relationship between columns in two different tables, where a column in one table refers to the Primary Key or a Unique Key in another table. A Foreign Key ensures that values entered in the foreign key column match values in the referenced primary key column or remain NULL.

Example: Creating a Table with a Foreign Key

      -- Create the 'Courses' table
      CREATE TABLE Courses (
        CourseID INT PRIMARY KEY,
        CourseName VARCHAR(100)
      );

      -- Create the 'Enrollments' table with a Foreign Key
      CREATE TABLE Enrollments (
        EnrollmentID INT PRIMARY KEY,
        StudentID INT,
        CourseID INT,
        FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
        FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
      );
    

In this example, 'StudentID' in the 'Enrollments' table is a Foreign Key that references 'StudentID' in the 'Students' table, and 'CourseID' references 'CourseID' in the 'Courses' table. This ensures that the enrollment record must correspond to an existing student and course.

3. Unique Constraint

The Unique constraint ensures that all values in a column are unique, meaning no two rows can have the same value for this column. Unlike the Primary Key, a table can have multiple Unique constraints, and columns with a Unique constraint can allow NULL values (unless explicitly stated otherwise).

Example: Creating a Table with a Unique Constraint

      -- Create the 'Employees' table with a Unique constraint on 'Email'
      CREATE TABLE Employees (
        EmployeeID INT PRIMARY KEY,
        Name VARCHAR(100),
        Email VARCHAR(100) UNIQUE
      );
    

In this example, the 'Email' column is constrained to have unique values. No two employees can have the same email address.

4. Not Null Constraint

The Not Null constraint ensures that a column cannot have a NULL value. This is useful for columns that are required to have valid data, such as names, dates, or IDs.

Example: Creating a Table with a Not Null Constraint

      -- Create the 'Customers' table with 'Name' as Not Null
      CREATE TABLE Customers (
        CustomerID INT PRIMARY KEY,
        Name VARCHAR(100) NOT NULL,
        Address VARCHAR(255)
      );
    

In this example, the 'Name' column cannot have a NULL value, ensuring that every customer must have a name.

5. Check Constraint

The Check constraint is used to limit the values that can be stored in a column. It allows you to define a condition that must be true for the data to be inserted or updated. If the condition is not met, an error will occur.

Example: Creating a Table with a Check Constraint

      -- Create the 'Products' table with a Check constraint on 'Price'
      CREATE TABLE Products (
        ProductID INT PRIMARY KEY,
        ProductName VARCHAR(100),
        Price DECIMAL(10, 2),
        CHECK (Price >= 0)
      );
    

In this example, the Check constraint ensures that the 'Price' column must always have a value greater than or equal to 0. Negative prices are not allowed.

6. Default Constraint

The Default constraint is used to provide a default value for a column when no value is specified during the insert. This ensures that a column always has a value, even if none is provided by the user.

Example: Creating a Table with a Default Constraint

      -- Create the 'Orders' table with a Default constraint on 'Status'
      CREATE TABLE Orders (
        OrderID INT PRIMARY KEY,
        OrderDate DATE,
        Status VARCHAR(20) DEFAULT 'Pending'
      );
    

In this example, if no value is provided for the 'Status' column when an order is inserted, the value will default to 'Pending'.

Conclusion

Constraints are essential for maintaining the integrity and validity of data in a database. They ensure that the data adheres to specific rules and standards, such as uniqueness, relationships, and non-null values. By understanding how to apply Primary Key, Foreign Key, Unique, Not Null, Check, and Default constraints, you can create robust and well-structured databases that maintain data integrity and consistency.