Declaring Constraints

## The two syntactic positions The body of `CREATE TABLE` is a comma-separated list of *elements*. Each element is either a **column definition** — a name, a data type, and optionally some constraints written inline after the type — or a **table constraint**, which stands on its own and names in parentheses the columns it applies to. ```sql -- column constraint: attaches to the column it follows price NUMERIC(10,2) NOT NULL CHECK (price > 0) -- table constraint: a list element in its own right CONSTRAINT ck_item_price CHECK (price > 0) ``` For a rule that touches exactly one column these two are equivalent. The engine records a constraint on the table either way; where you typed it does not survive into behaviour, does not change when the rule is evaluated, and does not change the error you get when a write violates it. ## What forces the table-level form The rule is purely mechanical: **a constraint that mentions more than one column cannot be written inline**, because inline syntax has no column list. That covers four common cases. - A composite primary key: `PRIMARY KEY (order_id, sku)`. - A composite unique key: `UNIQUE (team_id, jersey_no)`. - A composite foreign key: `FOREIGN KEY (dept_code, course_no) REFERENCES course (dept_code, course_no)`. - A CHECK relating columns: `CHECK (ends_at > starts_at)`. The composite primary key is where beginners actually get burned. Writing `PRIMARY KEY` inline on two different columns does not declare one key over the pair — it declares two separate primary keys, and since a table may have at most one, the statement is rejected: ```sql -- rejected: two PRIMARY KEY declarations, not one composite key CREATE TABLE enrollment ( student_id INTEGER PRIMARY KEY, course_id INTEGER PRIMARY KEY ); ``` The fix is a single table constraint: `PRIMARY KEY (student_id, course_id)`. ## NOT NULL is the exception in the other direction `NOT NULL` is defined by the standard as a *column* constraint only; there is no `NOT NULL (col)` table-constraint form. The nearest table-level equivalent is `CHECK (col IS NOT NULL)`, which expresses the same rule but is recorded as a check constraint — catalogs, information-schema views and schema-diff tools will report the column as nullable with a check attached rather than as a non-nullable column, and code generators keyed on nullability will get it wrong. Write `NOT NULL` inline. ## Naming works in either position The optional `CONSTRAINT <name>` prefix is legal in both positions, immediately before the constraint type: ```sql email VARCHAR(320) CONSTRAINT uq_users_email UNIQUE, ... CONSTRAINT uq_users_email UNIQUE (email) ``` That matters because a name is the handle you need later to drop or alter the constraint. ## A worked pair The same table, written both ways as far as each allows: ```sql -- mostly inline CREATE TABLE order_item ( order_id INTEGER NOT NULL REFERENCES orders (id), sku VARCHAR(32) NOT NULL, quantity INTEGER NOT NULL CHECK (quantity > 0), PRIMARY KEY (order_id, sku) -- two columns: must be table-level ); -- keys and rules gathered at table level, all named CREATE TABLE order_item ( order_id INTEGER NOT NULL, sku VARCHAR(32) NOT NULL, quantity INTEGER NOT NULL, CONSTRAINT pk_order_item PRIMARY KEY (order_id, sku), CONSTRAINT fk_order_item_ord FOREIGN KEY (order_id) REFERENCES orders (id), CONSTRAINT ck_order_item_qty CHECK (quantity > 0) ); ``` ## Choosing a style A common convention: keep `NOT NULL` and `DEFAULT` inline, since they read as properties of the column, and gather keys and multi-column rules at the bottom of the table body with explicit names. The bottom block then reads as a summary of the table's identity and relationships, and adding or removing a key shows up as a clean one-line diff instead of an edit buried in a column definition. ## Portability notes Two details differ between engines. First, an inline `REFERENCES parent (col)` is standard shorthand for a single-column foreign key, but engines differ in whether they record it as a real foreign key or merely parse it — writing foreign keys at table level avoids the question. Second, the standard says a column constraint may reference only the column it is attached to; some engines accept an inline CHECK that mentions other columns anyway. Put multi-column checks at table level and the code is unambiguous everywhere.