SQL schema check: what is wrong with it

What follows is not a made-up list but the real report for the schema on the left. You see the same findings in the editor under “Checks”, and many of them have a fix button there.

The inputschema.sql

CREATE TABLE user (
    id          BIGSERIAL PRIMARY KEY,
    email       VARCHAR(255) NOT NULL,
    balance     FLOAT,
    created_at  TIMESTAMP NOT NULL
);

CREATE TABLE orders (
    order_id    INTEGER NOT NULL,
    user_id     INTEGER REFERENCES user (id),
    total       FLOAT NOT NULL,
    placedAt    TIMESTAMP
);

Detected as: PostgreSQL

errors 1warnings 8hints 2

Open the editor

The script stays in the browser: both parsing and generation run on your machine, nothing goes to a server.

What it found

Sides of the link have different types
orders.user_id
The column and what it references are declared differently. Some databases refuse the key outright, the rest silently cast on every join.
Table without a primary key
orders
A row here cannot be addressed reliably: you cannot update one of two identical rows, or point at it from elsewhere. Replication and ORMs assume a key exists.
Foreign key without an index
orders
Joins and parent-delete checks run over this column. Without an index each of them is a full table scan.
Foreign key accepts NULL
orders.user_id
Sometimes that is the intent: the link is optional. More often NOT NULL was simply forgotten and rows without a parent appear.
Name is a reserved word
user
Every query would have to quote it. Renaming is cheaper than remembering the exception.
Money in an approximate type
user.balanceorders.total
FLOAT and REAL store numbers approximately: totals stop adding up after the first division. Money belongs in NUMERIC or DECIMAL.
TIMESTAMP without a time zone
user.created_atorders.placedAt
In PostgreSQL such a column keeps no zone and is silently read as local time. Values drift when the server moves or the clocks change — timestamptz is usually what is meant.
Mixed naming styles
orders.placedAt
snake_case and camelCase live side by side. Listed here are the names in the minority style: aligning them beats remembering exceptions.
No ON DELETE specified
orders
What happens to children when a parent is deleted is left to the database, which refuses the delete by default. If that is not the intent, say so explicitly.

SQL schema check: what is wrong with it

What it found

Sides of the link have different types

Table without a primary key

Foreign key without an index

Foreign key accepts NULL

Name is a reserved word

Money in an approximate type

TIMESTAMP without a time zone

Mixed naming styles

No ON DELETE specified

Nearby

Other databases