Creating tables with keys and rules

Define a table's columns, types and constraints, build tables and views from queries, and know which rules your database actually enforces.

Analysts read far more than they write, but sooner or later you’ll build a table: a cleaned-up copy of messy data, a lookup list, or a staging table before dbt is in place. This lesson covers defining one well. Lesson 9 covered choosing data types. This one adds the rules a table enforces on its rows.

In the sandbox, a table you create lasts until you press Reset. Running the same CREATE TABLE twice gives a “table already exists” error, so Reset, or start with DROP TABLE IF EXISTS quotes;.

The shape

CREATE TABLE quotes (
    quote_id        INTEGER       PRIMARY KEY,
    opportunity_id  INTEGER       NOT NULL REFERENCES opportunities (opportunity_id),
    status          VARCHAR(20)   NOT NULL DEFAULT 'Draft',
    total           NUMERIC(12,2) NOT NULL CHECK (total >= 0),
    created_at      DATE          NOT NULL DEFAULT CURRENT_DATE,
    expires_at      DATE
);

Each line is a column name, its type, then any rules. Those rules are called constraints:

Constraints doing their job

INSERT INTO quotes (quote_id, opportunity_id, total) VALUES (1, 1003, 12500);
-- works: status becomes 'Draft', created_at becomes today
 
INSERT INTO quotes (quote_id, opportunity_id, total) VALUES (2, 1003, -5);
-- error: CHECK constraint failed: total >= 0
 
INSERT INTO quotes (quote_id, opportunity_id) VALUES (3, 1003);
-- error: NOT NULL constraint failed: quotes.total
 
INSERT INTO quotes (quote_id, opportunity_id, total) VALUES (1, 1003, 500);
-- error: quote_id 1 already exists

Run them one at a time: in the sandbox, the first error stops everything after it. Each rule stops bad data at the door, so nobody has to find it later in a report. A rejected row is far cheaper than a wrong dashboard.

DEFAULT only applies when you leave the column out. Inserting NULL on purpose still means NULL, which NOT NULL then rejects.

Which rules are actually enforced

Not every database enforces every constraint. This matters more than it sounds:

So in the sandbox, INSERT ... VALUES (4, 9999, 500) succeeds even though deal 9999 doesn’t exist. Turn foreign keys on, and the same insert fails. When a database doesn’t enforce a rule, you have to check it with queries, like lesson 26’s NOT EXISTS.

Building a table from a query

CREATE TABLE ... AS makes a table from any SELECT, with no column list needed:

CREATE TABLE won_2026 AS
SELECT opportunity_id, owner_id, amount, closed_at
FROM opportunities
WHERE is_won = 1
  AND closed_at >= '2026-01-01';

That’s a snapshot: 13 rows, copied once. Later changes to opportunities don’t reach it, and it gets no constraints, keys or defaults.

Views: a saved query

A view stores the query, not the rows. Every time you select from it, it runs fresh:

CREATE VIEW open_pipeline AS
SELECT opportunity_id, owner_id, stage, amount
FROM opportunities
WHERE closed_at IS NULL;
 
SELECT stage, SUM(amount) FROM open_pipeline GROUP BY stage;

Use a view to give everyone the same definition of “open pipeline”, so it’s written once instead of in every report.

Changing and removing tables

ALTER TABLE quotes ADD COLUMN discount NUMERIC(12,2) DEFAULT 0;   -- add a column
DROP TABLE won_2026;                                              -- delete the table and its data
DROP VIEW open_pipeline;

DROP TABLE has no undo on most databases. Treat it like DELETE without a WHERE (lesson 16).

Common mistakes

Dialect notes

Snowflake notes

Snowflake enforces only NOT NULL. PRIMARY KEY, UNIQUE and REFERENCES are recorded as documentation but don’t stop duplicates or orphans, and CHECK isn’t supported at all. On Snowflake, data quality comes from tests instead: dbt’s unique, not_null, relationships and accepted_values tests run as queries after each load. Snowflake also has CREATE OR REPLACE TABLE to rebuild a table in one step, and CLONE for an instant copy.

More in the field guide: CREATE TABLE / CTAS, CREATE VIEW, Where constraints go and Finding the problems Snowflake lets in. Exercise T1 designs a similar quotes table.

Try it

Create a quotes table with a primary key quote_id, a required opportunity_id that points to a real deal, a required status that defaults to Draft, a required total that can’t be negative, and an optional expires_at date. Add quote 1 for deal 1003 with a total of 12,500 and no status, then quote 2 for deal 1012 with a total of 8,000 and the status Sent. Finish with a SELECT of quote_id, opportunity_id, status and total, sorted by quote_id.

The check runs your statements on a fresh copy of the data, so a quotes table you made earlier won’t get in the way. It can only compare the rows you return, so it confirms your DEFAULT but can’t see your other rules. Test those yourself after it passes: insert a negative total, then a quote with no opportunity_id. Both should fail.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson