Column Constraints

Definition

A column constraint is written inline, directly as part of a single column's definition within CREATE TABLE (or ALTER TABLE ... ADD COLUMN). It is visually and logically attached to exactly one column and can only ever govern that one column.

Syntax Pattern

sql
CREATE TABLE employees (
    emp_id     INT PRIMARY KEY,
    email      VARCHAR(150) UNIQUE NOT NULL,
    salary     NUMERIC(10,2) CHECK (salary > 0),
    department VARCHAR(50) DEFAULT 'General',
    hire_date  DATE NOT NULL
);

Here PRIMARY KEY, UNIQUE, NOT NULL, CHECK (salary > 0), and DEFAULT 'General' are all column constraints — each sits right after its one column's type declaration, with no separate clause and no explicit column-name reference needed (the column is implicit: whichever one it's attached to).

What Column Constraints Can and Cannot Express

They work for any rule that only needs to look at a single column's own value: not-null-ness, a single-column uniqueness rule, a single-column range check, a single-column default, or a single-column foreign key reference.

They cannot express a rule that needs to see more than one column at once. CHECK (salary > 0) is fine as a column constraint because it only touches salary. CHECK (check_out > check_in) is impossible to write as a column constraint, because there is no single column to attach it to that also "sees" the other column — the database would have no clear notion of which column definition the rule belongs to. This exact impossibility is what makes table constraints necessary, covered in the next topic.

Multiple Column Constraints on One Column

You can stack several column constraints on the same column, in any order (though style guides often standardize an order like type → NOT NULL → DEFAULT → CHECK → UNIQUE):

sql
username VARCHAR(50) NOT NULL UNIQUE DEFAULT 'guest' CHECK (LENGTH(username) >= 3)

Column Constraints in ALTER TABLE ... ADD COLUMN

sql
ALTER TABLE employees ADD COLUMN bonus NUMERIC(10,2) DEFAULT 0 CHECK (bonus >= 0);

The same inline pattern applies when adding a brand-new column to an existing table.

Edge Cases

• A column constraint can still indirectly involve a subquery or function call, e.g. CHECK (price BETWEEN 0 AND 100000), as long as everything it evaluates comes from that one column's value at that row — it's the "how many columns does the expression reference," not the "is it syntactically fancy," that determines column- vs table-level necessity.
• Anonymous (unnamed) column constraints are extremely common in casual schema code, but per Topic 10.7, naming them explicitly (salary NUMERIC(10,2) CONSTRAINT chk_salary_pos CHECK (salary > 0)) is still fully valid column-level syntax — "column-level" and "named" are independent, orthogonal choices.
• Some engines allow a column constraint to reference the same table's other rows implicitly through CHECK — this is not portable/reliable and is generally disallowed or ignored by most engines; don't rely on cross-row CHECK logic.

Key Takeaways / Q&A

Q: What single test determines whether a constraint MUST be table-level rather than column-level? A: Whether the constraint's boolean/rule expression needs to reference more than one column.

Q: Can a column constraint be named? A: Yes — being column-level and being named are independent; you can write CONSTRAINT name CHECK(...) inline on a single column.

Q: Give an example of a rule impossible to express as a column constraint. A: CHECK (check_out > check_in) — it needs two columns, so it must be a table constraint.