Column Constraints vs Table Constraints in SQL: What’s the Difference?

Posted on by Ian

In relational database management systems (RDBMSs), constraints are nifty tools that we can use to ensure the integrity, accuracy, and reliability of the data stored in our database.

Constraints can enforce rules at the column and table levels, guiding how data can be inserted, updated, or deleted. Whether you’re defining the uniqueness of a value, establishing relationships between tables, or ensuring that critical fields are never left blank, constraints play an important role in the design of relational databases.

When we add a constraint to a table, we can implement it at the column level, or the table level. In other words, we can implement it as a column constraint or a table constraint.

Let’s take a look at the differences between column constraints and table constraints.

What Are SQL Constraints?

SQL constraints are rules applied to data columns or entire tables to maintain the integrity and accuracy of the data within the database. These constraints ensure that the data entered into a database adheres to the predefined rules, preventing invalid or corrupt data from being stored.

Some of the most common types of constraints in SQL include:

  • PRIMARY KEY: Ensures that each row in a table is unique and that no NULL values are allowed.
  • UNIQUE: Enforces the uniqueness of the values in one or more columns.
  • FOREIGN KEY: Establishes a relationship between two tables, ensuring referential integrity.
  • CHECK: Validates that data entered into a column meets a specific condition.
  • NOT NULL: Ensures that a column cannot have a NULL value.

These constraints are foundational to maintaining data integrity, preventing anomalies, and enforcing business rules within the database.

Column Constraints

Column constraints are rules applied directly to individual columns within a table. These constraints focus on ensuring the integrity of the data within a specific column, making sure that each entry in the column adheres to the defined rules.

Table Constraints

Table constraints, on the other hand, are applied at the table-level and can span across multiple columns within a table. These constraints are often used when the rules need to involve more than one column or when the relationship between columns needs to be enforced. That said, it’s also possible to create a table-level constraint that only constrains one column.

Differences Between Column Constraints and Table Constraints

We’ve already touched on the main difference between column and table constraints; column constraints are applied to a single column, whereas table constraints are applied at the table level.

So from a SQL coding perspective, we define column constraints in the column definition itself, whereas, we define table constraints after the column definitions. We can also add them by modifying the table after it has been created.

Creating a Column Constraint

Here’s an example of creating column constraints:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE
);

All of these constraints were created within the column, including the PRIMARY KEY constraint, the NOT NULL constraints, and the UNIQUE constraint.

Creating a Table Constraint

We can also define the constraints after all the column definitions:

CREATE TABLE Employees (
    EmployeeID INT NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100),
    CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID),
    CONSTRAINT UQ_Email UNIQUE (Email)
);

This is where they cross over to be table constraints. We can use this syntax to apply constraints against multiple columns if we want (example of this later).

Note however, that we kept the NOT NULL constraints within the column definitions. That’s because NOT NULL constraints can only be defined as part of a single column, and therefore can only be column constraints. This makes them a bit different to the other constraints in that regard.

And another way to do it is to add the constraints in completely separate statements:

-- Create the table
CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
);

-- Add the constraints
ALTER TABLE Employees
ALTER COLUMN EmployeeID VARCHAR(50) NOT NULL;

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

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

ALTER TABLE Employees
ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID);

ALTER TABLE Employees
ADD CONSTRAINT UQ_Email UNIQUE (Email);

Here, we created the table without any constraints, and we then added the constraints later using the ALTER TABLE statement.

Note that when it came to the NOT NULL constraints, we had to redefine the whole column, due to NOT NULL being a constraint that is tied to the column definition.

We also had to add a NOT NULL constraint to the EmployeeID column before we attempted to add the PRIMARY KEY constraint. Primary keys can only be added to columns that are NOT NULL.

So basically, the PRIMARY KEY and UNIQUE constraints can be considered table constraints, given they were defined outside of the column.

Creating Table Constraints Against Multiple Columns

As mentioned, table constraints can be applied against multiple columns. So for example, we could do this:

CREATE TABLE Orders (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    Price DECIMAL(10, 2),
    PRIMARY KEY (OrderID, ProductID),
    CHECK (Quantity > 0 AND Price > 0)
);

Here’s what the constraints do:

  • Primary Key: The PRIMARY KEY constraint is applied to both OrderID and ProductID columns, making the combination of these two columns unique and serving as the primary identifier for each record.
  • Check Constraint: The CHECK constraint ensures that both Quantity and Price are greater than 0 within a single constraint. If either of the conditions fails (i.e., Quantity <= 0 or Price <= 0), the record will not be inserted. This is a table constraint that couldn’t be implemented as a column constraint. That’s not to say that we couldn’t implement two separate column constraints to achieve the same effect in this case, but there could be cases where we can only do it with a table constraint.

And here’s an example of adding the constraints in separate statements (after the table has been created without the constraints):

-- Create the table
CREATE TABLE Orders (
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT,
    Price DECIMAL(10, 2)
);

-- Add the constraints
ALTER TABLE Orders
ADD CONSTRAINT PK_Orders PRIMARY KEY (OrderID, ProductID);

ALTER TABLE Orders
ADD CONSTRAINT CHK_Orders_Quantity_Price CHECK (Quantity > 0 AND Price > 0);

This time we included the NOT NULL constraints in the column definitions when we created the table. This saves us from having to redefine the whole column in a separate ALTER TABLE statement.