SQL Constraints

Welcome to The Coding College! In this tutorial, we’ll explore SQL Constraints, essential tools in database design that ensure data integrity and enforce rules at the table level.

What are SQL Constraints?

SQL Constraints are rules applied to table columns to restrict the types of data that can be stored. They ensure accuracy, consistency, and reliability of the data within the database.

Constraints can be applied at two levels:

  1. Column-Level Constraints: Applied to a single column.
  2. Table-Level Constraints: Applied to the entire table.

Types of SQL Constraints

  1. NOT NULL
  2. UNIQUE
  3. PRIMARY KEY
  4. FOREIGN KEY
  5. CHECK
  6. DEFAULT

Detailed Overview of Constraints

1. NOT NULL

Ensures that a column cannot have a NULL value.

Syntax:

CREATE TABLE Employees (  
    ID INT NOT NULL,  
    Name VARCHAR(50) NOT NULL  
);

Use Case: Ensures critical fields, such as ID or Name, are always populated.

2. UNIQUE

Ensures that all values in a column are unique.

Syntax:

CREATE TABLE Students (  
    StudentID INT UNIQUE,  
    Email VARCHAR(100) UNIQUE  
);

Note: Multiple UNIQUE constraints can be applied to a table.

3. PRIMARY KEY

A combination of NOT NULL and UNIQUE. Uniquely identifies each record in a table.

Syntax:

CREATE TABLE Orders (  
    OrderID INT PRIMARY KEY,  
    OrderDate DATE  
);
  • A table can only have one PRIMARY KEY.
  • A PRIMARY KEY can consist of single or multiple columns (composite key).

4. FOREIGN KEY

Ensures referential integrity by creating a link between two tables.

Syntax:

CREATE TABLE Orders (  
    OrderID INT PRIMARY KEY,  
    CustomerID INT,  
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)  
);

Use Case: Links Orders to Customers ensuring valid CustomerID values.

5. CHECK

Ensures that all column values satisfy a specific condition.

Syntax:

CREATE TABLE Orders (  
    OrderID INT PRIMARY KEY,  
    CustomerID INT,  
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)  
);

Use Case: Enforces logical business rules, such as age restrictions.

6. DEFAULT

Sets a default value for a column when no value is specified.

Syntax:

CREATE TABLE Products (  
    ProductID INT,  
    InStock BOOLEAN DEFAULT TRUE  
);

Use Case: Automatically assigns default values to reduce manual input.

Adding Constraints to Existing Tables

Constraints can be added to existing tables using the ALTER TABLE statement.

Add NOT NULL:

ALTER TABLE Employees  
ALTER COLUMN Name VARCHAR(50) NOT NULL;

Add UNIQUE:

ALTER TABLE Students  
ADD CONSTRAINT Unique_Email UNIQUE (Email);

Add FOREIGN KEY:

ALTER TABLE Orders  
ADD CONSTRAINT FK_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

Add CHECK:

ALTER TABLE Employees  
ADD CONSTRAINT Check_Age CHECK (Age >= 18);

Add DEFAULT:

ALTER TABLE Products  
ADD DEFAULT TRUE FOR InStock;

Dropping Constraints

Constraints can be removed using the ALTER TABLE statement.

Drop UNIQUE:

ALTER TABLE Students  
DROP CONSTRAINT Unique_Email;

Drop FOREIGN KEY:

ALTER TABLE Orders  
DROP CONSTRAINT FK_CustomerID;

Best Practices

  1. Design for Integrity: Use constraints to enforce data integrity at the database level.
  2. Performance Considerations: Avoid unnecessary constraints that may impact database performance.
  3. Descriptive Names: Use meaningful constraint names to simplify management.
  4. Backup Before Changes: Always back up the database before altering constraints.

Common Errors

  1. Violation of Constraints:
    • Error: Cannot insert duplicate values in UNIQUE column.
    • Solution: Ensure data adheres to the constraint rules.
  2. Dependent Constraints:
    • Error: Cannot drop a table with FOREIGN KEY constraints.
    • Solution: Drop dependent constraints first.
  3. Incorrect Syntax:
    • Error: Syntax error in CHECK constraint.
    • Solution: Verify DBMS-specific syntax.

Conclusion

SQL Constraints are powerful tools for maintaining data quality and integrity. They reduce errors and ensure that your database follows the rules of your application’s domain.

Leave a Comment