Creating Constraints in the CREATE TABLE Statement with SQL

Creating Constraints in the CREATE TABLE Statement

When defining constraints during table creation, you specify rules that the data must follow. These constraints can be applied directly to individual columns or to multiple columns collectively. Here’s a detailed breakdown of how to define constraints in the CREATE TABLE statement.

Primary Key Constraint

The PRIMARY KEY constraint ensures that each row in the table is uniquely identifiable and that no null values are allowed in the primary key column(s).

Syntax 

CREATE TABLE table_name (
    column_name data_type PRIMARY KEY
);

Example 

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

In this example, EmployeeID is the primary key for the Employees table. It ensures that every EmployeeID is unique and not null.

Foreign Key Constraint

The FOREIGN KEY constraint establishes a relationship between columns in two tables. It ensures that the value in the foreign key column exists in the referenced column of another table.

Syntax 

CREATE TABLE table_name (
    column_name data_type,
    foreign_key_column data_type,
    FOREIGN KEY (foreign_key_column) REFERENCES other_table(primary_key_column)
);

Example 

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    EmployeeID INT,
    OrderDate DATE,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

Here, EmployeeID in the Orders table is a foreign key that references the EmployeeID in the Employees table. This ensures that every EmployeeID in Orders must exist in Employees.

Unique Constraint

The UNIQUE constraint ensures that all values in a column or a combination of columns are unique across the table. It allows for null values but no duplicate non-null values.

Syntax 

CREATE TABLE table_name (
    column_name data_type UNIQUE
);

Example 

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) UNIQUE
);

In this example, ProductName must be unique within the Products table.

Not Null Constraint

The NOT NULL constraint ensures that a column cannot have null values, which means it must contain a value.

Syntax 

CREATE TABLE table_name (
    column_name data_type NOT NULL
);

Example 

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL
);

Here, CustomerName cannot be null; each row must have a value for this column.

Check Constraint

The CHECK constraint ensures that all values in a column satisfy a specified condition or set of conditions.

Syntax 

CREATE TABLE table_name (
    column_name data_type,
    CHECK (condition)
);

Example 

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Salary DECIMAL(10, 2),
    CHECK (Salary >= 0)
);

In this example, the CHECK constraint ensures that Salary cannot be negative.

Default Constraint

The DEFAULT constraint provides a default value for a column when no value is specified during an insert operation.

Syntax 

CREATE TABLE table_name (
    column_name data_type DEFAULT default_value
);

Example 

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    StockQuantity INT DEFAULT 0
);

Here, StockQuantity will default to 0 if no value is provided during insertion.

Composite Key Constraint

Composite keys involve multiple columns used together as a primary key or unique constraint. They are used when a single column is not sufficient to uniquely identify rows.

Syntax 

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
    PRIMARY KEY (column1, column2)
);

Example 

CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

In this example, the combination of OrderID and ProductID uniquely identifies each row in the OrderDetails table.

Table-Level Constraints

Table-level constraints are defined after the column definitions. They are useful for constraints that involve multiple columns.

Syntax 

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
    CONSTRAINT constraint_name CONSTRAINT_TYPE (column1, column2)
);

Example 

CREATE TABLE Sales (
    SaleID INT,
    ProductID INT,
    SaleDate DATE,
    CONSTRAINT unique_sale UNIQUE (SaleID, ProductID)
);

Here, a UNIQUE constraint named unique_sale ensures that each combination of SaleID and ProductID is unique.

Conclusion

Defining constraints in the CREATE TABLE statement is vital for ensuring data integrity and enforcing business rules in your database schema. By using constraints such as primary keys, foreign keys, unique constraints, and others, you ensure that your data adheres to required rules and maintains consistency. Constraints can be applied at both the column level and the table level, providing flexibility in how you design and enforce data rules.