Column defaults, identity columns and keys

A column that an INSERT leaves out is not always NULL. A table can say what such a column holds, number its rows for you, and describe which columns identify a row.

How it works

When an INSERT says nothing about a column, the table can fill it with a DEFAULT. The table can also write ids itself, with an identity column. A primary key says which columns identify a row.

Common mistake

A default fills a column only when the statement says nothing about it. A NULL that you write is a value, and it is stored.

Worked example

CREATE TEMP TABLE members (
  email STRING,
  tier STRING DEFAULT 'basic',
  points INT64 DEFAULT 0
);
INSERT INTO members (email) VALUES ('[email protected]');
INSERT INTO members (email, tier) VALUES ('[email protected]', 'plus');
ALTER TABLE members ALTER COLUMN tier SET DEFAULT 'trial';
INSERT INTO members (email, tier, points) VALUES ('[email protected]', DEFAULT, 10);
UPDATE members SET tier = DEFAULT WHERE email = '[email protected]';
UPDATE members SET points = NULL WHERE email = '[email protected]';
SELECT email, tier, points FROM members ORDER BY email;

Ana's insert names only the email, so she gets 'basic' and 0. Ben's insert names a tier, so he gets 'plus'.

SET DEFAULT 'trial' changes the default for the rows that come after it. Cy writes DEFAULT for tier and gets 'trial'.

The two updates change rows that are already there. SET tier = DEFAULT gives Ben the default as it is now, 'trial'. SET points = NULL stores a NULL for Ana.

All BigQuery lessons