iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To create a table, use CREATE TABLE to name it, define its columns and data types, and add any rules the data must satisfy. How do I create a table in a database, and what are the two ways to define its constraints? In PostgreSQL 18, a constraint that applies to one column can sit beside that column’s definition; a table constraint is written as a separate element and can cover multiple columns.
Start with the table’s columns and types
A table definition gives each column a name and a data type. Choose types that fit the values you intend to store; type names and available options differ among database products. This PostgreSQL 18 example defines a customer table with an identifier, an email address, and a timestamp:
CREATE TABLE customers (
customer_id integer PRIMARY KEY,
email varchar(254) NOT NULL,
created_at timestamp DEFAULT CURRENT_TIMESTAMP
);PRIMARY KEY identifies rows uniquely and does not allow a null key. NOT NULL requires an email value. The DEFAULT supplies a value when an insert omits created_at. These type spellings and default behavior are shown for PostgreSQL; check the documentation for the database and version you use.
Recommended Free Tools
Approach 1: Put a single-column constraint beside its column
A column constraint follows the column’s type in the table definition. This keeps a rule visually close to the field it governs. For example, PostgreSQL documents column definitions such as:
#1 Best Overall
code char(5) CONSTRAINT firstkey PRIMARY KEY,
name varchar(40) NOT NULL CHECK (name <> '')The first definition gives the primary-key constraint the name firstkey. The second requires a name and checks that it is not an empty string. A check condition should express a meaningful rule for the values in that column.
Approach 2: Write a table constraint as a separate element
A table constraint appears separately from the column definitions, usually after them and separated by a comma. It can apply to one column or span several columns. For example, this PostgreSQL 18 definition prevents the same customer and group pairing from appearing twice:
CREATE TABLE memberships (
customer_id integer NOT NULL,
group_id integer NOT NULL,
CONSTRAINT uq_membership UNIQUE (customer_id, group_id)
);The named constraint applies to the pair as a whole: a customer may belong to different groups, and a group may include different customers, but the same pairing cannot be duplicated. Table-level form is particularly useful for multi-column rules and for giving a rule a clear name that is easier to recognize when maintaining the schema.
Choose the form by scope and readability
| Consideration | Column constraint | Table constraint |
|---|---|---|
| Scope | Convenient for a rule that affects one column. | Can cover one column or multiple columns. |
| Placement | Written with the column’s name and type. | Written as a separate element in the table definition. |
| Readability | Keeps a local rule beside its field. | Groups broader rules separately from field definitions. |
| Naming and maintenance | Can be named, as in the firstkey example. |
Can be named, as in uq_membership, which makes the rule easy to identify. |
For a single-column rule, either form may be available. PostgreSQL describes the column form as a notational convenience when the constraint affects only one column. The separate form becomes necessary for a rule involving multiple columns. Use the form that makes the rule’s scope clearest to someone reading or changing the schema.
Rank #3
Know what the main constraints enforce
- Primary key: identifies each row uniquely and requires non-null key values.
- Unique: prevents duplicate values across the constrained column or combination of columns.
- Foreign key: requires referenced values to correspond to values in a referenced table, subject to the database’s rules and options. MySQL 8.4 describes the relationship as a parent table holding initial values and a child table containing values that reference them. See the MySQL 8.4 foreign-key documentation.
- Check: enforces a condition on row values, such as requiring a numeric value to be greater than zero.
- Not null: disallows a missing value in the column.
Foreign keys are commonly used to express relationships between tables; their supported options and enforcement details can vary by database engine. Do not assume a clause or behavior in one product works identically in another.
Practical workflow for creating a table
- Decide row identity. Identify which value or combination of values uniquely identifies a row, then define a primary key.
- Select data types. Choose a type for each field and confirm its spelling and behavior in the target database’s documentation.
- Mark required values. Add
NOT NULLwhere a value must be present, and use a default only when an omitted value should be filled automatically. - Express business rules. Add unique or check constraints for invariants that the database should enforce.
- Define relationships. Add foreign keys where values must refer to rows in another table.
- Run the statement on the intended database version. If it fails, inspect the reported syntax or constraint error and verify that the types and clauses are supported by that engine.
Check the syntax for your database
The examples above are labeled for PostgreSQL 18, whose official CREATE TABLE reference documents column and table constraints, primary keys, unique constraints, checks, foreign keys, and defaults. PostgreSQL’s table-creation tutorial introduces table names, columns, and data types. Other products and versions may use different data types, options, or constraint behavior. Treat the examples as PostgreSQL syntax, not as a guarantee of universal SQL portability.
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.
Free tools Windows power users keep installed
One-click scans. No signup required.
