DDL and DML

Existing rows get NULL unless the new column declares a DEFAULT, in which case every existing row is populated with that default value. Adding a NOT NULL column with no DEFAULT to a table that already holds rows is rejected.

Any rule covering more than one column — a composite PRIMARY KEY, UNIQUE or FOREIGN KEY, or a CHECK comparing two columns — must be written as a table constraint after the column list. Single-column rules may be written either way.

DEFAULT names the value the engine stores when an INSERT supplies none for that column. The expression is evaluated at insert time, so each row records its own insertion moment rather than one value frozen when the table was created.

FLOAT and REAL store binary approximations, so a value such as 0.10 is never held exactly and the error accumulates across sums and multiplications. Money needs an exact type: DECIMAL/NUMERIC with a declared precision and scale.

Standard SQL uses an identity column: id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY. The engine supplies each value from an attached sequence generator, whose START WITH, INCREMENT BY, CYCLE and MINVALUE/MAXVALUE options you set in parentheses.

It removes every whole row of orders whose status is 'DRAFT'; the table and all other rows stay. With no WHERE clause, DELETE FROM orders removes every row in the table.

An INSERT without a column list binds values to columns by position, so adding, dropping or reordering a column silently shifts every value. Naming the columns pins each value to its column and lets omitted columns take their declared defaults.

MERGE joins a target table to a source rowset through an ON condition, then applies one action per source row: WHEN MATCHED THEN UPDATE for keys that already exist in the target, WHEN NOT MATCHED THEN INSERT for the rest.

RETURNING makes the INSERT also yield a result set: one row per inserted row, containing the listed columns with their final stored values — generated identity keys, applied defaults and computed columns included.

TRUNCATE TABLE empties a table in one bulk operation: no WHERE clause, no per-row DELETE triggers, usually an identity reset, and far less logging. DELETE removes rows one at a time as ordinary DML and can be filtered.

START TRANSACTION (or BEGIN) opens an explicit transaction; COMMIT ends it and makes all its work permanent; ROLLBACK ends it and discards all its work. Without them, autocommit wraps each statement in its own transaction that commits immediately.

ROLLBACK TO SAVEPOINT undoes only the statements executed after that savepoint. The transaction stays open, everything done before the savepoint is still pending, and you must still issue COMMIT or ROLLBACK to finish.

It depends on the engine. MySQL and Oracle commit implicitly before and after DDL, so a later ROLLBACK undoes nothing that came earlier. PostgreSQL, SQL Server and SQLite treat DDL as transactional and roll it back with everything else.

SET TRANSACTION sets a transaction's characteristics — its isolation level and its access mode, READ ONLY or READ WRITE — not any data. It must be issued before the transaction executes its first query or data-modifying statement, or the engine rejects it.

s1 itself stays established and can be rolled back to again. Every savepoint created after s1 is destroyed by the rewind, and naming one afterwards raises an invalid-savepoint error rather than doing nothing.