To create a table, use SQL’s CREATE TABLE statement to name the table, define its columns and choose a data type for each. You can put a constraint beside the column it governs, or write it separately as a table constraint; the separate form can cover multiple columns. This guide uses PostgreSQL 18 syntax. If you’re asking, “How do I create a table in a database, and what are the two ways to define its constraints?”, the key distinction is scope: a column constraint is attached to one field, while a table constraint belongs to the table definition and can govern one or more fields.
Start with the table and its columns
A table stores rows with the same set of fields. In a CREATE TABLE statement, give the table a meaningful name, list each column, and choose a type suited to the values it will store. The example below uses PostgreSQL 18 syntax:
As an Amazon Associate I earn from qualifying purchases.
CREATE TABLE customers (
customer_id integer,
email varchar(320),
created_at timestamp
);This creates the columns but does not yet declare which values are required, which identify a row, or whether an email must be unique. Add constraints to express those rules in the database. PostgreSQL documents the statement and its available forms in its CREATE TABLE reference, while its table-creation tutorial introduces column names and types.
Recommended Free Tools
What the main constraints do
- Primary key: identifies rows; its values must be unique and non-null.
- Unique: prevents duplicate values across the column or set of columns it targets.
- Foreign key: requires referenced values to correspond to values in another table, supporting relationships between tables.
- Check: requires row values to satisfy a specified condition.
- Not null: prevents a column from being left without a value.
A default supplies a value when an insert omits a column; it is not itself a constraint. The precise types, syntax, and enforcement details depend on the database product and version.
#1 Best Overall
Approach 1: Put a constraint beside its column
A column constraint appears within that column’s definition, after its type. It is convenient when the rule applies only to that field, because the rule is visible where the field is declared. Here is a PostgreSQL 18 example:
CREATE TABLE customers (
customer_id integer CONSTRAINT customers_pkey PRIMARY KEY,
email varchar(320) NOT NULL UNIQUE,
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);The named primary key makes customer_id the row identifier. The email is required and must be unique. The timestamp is required and receives a default when an insert does not provide one. PostgreSQL allows a constraint to be named with CONSTRAINT; the name can help identify it in later maintenance and error messages.
PostgreSQL’s documentation gives column-level examples such as code char(5) CONSTRAINT firstkey PRIMARY KEY and name varchar(40) NOT NULL CHECK (name <> ''). These show how a single-column rule can sit directly beside the field it affects.
Approach 2: Declare a table constraint separately
A table constraint is a separate element in the table definition, rather than part of one column’s line. It can govern multiple columns, making it the natural form for composite rules. For example, a customer may join a group only once:
CREATE TABLE memberships (
customer_id integer NOT NULL,
group_id integer NOT NULL,
joined_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT uq_membership UNIQUE (customer_id, group_id)
);Here, uq_membership prevents duplicate pairs while allowing a customer to belong to different groups and a group to have different customers. The rule’s name makes its purpose easier to recognize than an unnamed constraint.
A table constraint can also express a single-column rule. PostgreSQL describes column constraints as a notational convenience when a constraint affects one column; the table form is not tied to a single column and can encompass more than one. Use the form that makes the rule’s scope clearest.
Which form should you choose?
| Consideration | Column constraint | Table constraint |
|---|---|---|
| Scope | Best suited to a rule on one column. | Can cover one or multiple columns. |
| Readability | Keeps a field’s local rules beside its type. | Groups table-wide or multi-column rules with the other table-level rules. |
| Naming and maintenance | A constraint can be named, but several rules may make a column definition harder to scan. | A named rule can make a composite or broader condition easier to identify and maintain. |
For a rule involving a pair or group of columns, use a table constraint. For a rule limited to one field, placing it beside that field is often easier to read. Either way, constraint scope and database support matter more than visual preference.
Define relationships with foreign keys
A foreign key connects values in a child table to a referenced key in a parent table. For instance, if a customers table has a primary key, a membership row can refer to an existing customer:
Best Value
CREATE TABLE memberships (
customer_id integer NOT NULL,
group_id integer NOT NULL,
CONSTRAINT fk_memberships_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
);This example shows a table-level foreign key. It prevents a membership from referring to a customer ID absent from the referenced table. MySQL’s 8.4 manual likewise describes the parent-and-child relationship and documents its own foreign-key rules and referential actions: MySQL 8.4 FOREIGN KEY Constraints. Its documentation is a useful reminder that syntax and behavior should be checked for the database you actually use.
Practical checklist before running CREATE TABLE
- Choose a table name and list the fields each row needs.
- Choose a type for every field, checking that the spelling and behavior are supported by your target database and version.
- Decide what uniquely identifies each row, and declare that primary key.
- Mark values that must always be present as
NOT NULL. - Add unique and check constraints for business rules that should be enforced in the database.
- Add foreign keys where values must refer to rows in other tables.
- Run the statement against the intended database, review any error, and inspect the created table definition.
Account for database dialects
SQL syntax is not identical across database products. PostgreSQL 18 examples here illustrate column and table constraints, but type names, optional clauses, and enforcement details may differ elsewhere. MySQL’s 8.4 foreign-key reference documents its own rules; do not assume that a PostgreSQL-specific type or clause works unchanged in MySQL or another system. Check the reference manual for the database and version you plan to run.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
