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.
name TYPE DEFAULT valuein aCREATE TABLEgives a column a default. A default is a literal or a call to a few functions, such asCURRENT_TIMESTAMP(). It cannot read another column, and its type has to fit the column:DEFAULT 5on aSTRINGcolumn is refused.- A default fills a column in two cases: the
INSERTleaves the column out of its column list, or itsVALUESrow writes the keywordDEFAULTin the place of the column. AnINSERTwith no column list has to give every column, in the order of the table. A column with noDEFAULTclause has the defaultNULL. NULLwritten in aVALUESrow is a value, so it is stored as it is.UPDATE t SET col = DEFAULTputs the default back.ALTER TABLE t ALTER COLUMN col SET DEFAULT valuesets or changes a default, andDROP DEFAULTremoves it. Either one changes the rows written after it. The rows already in the table keep what they hold.- A table made by
CREATE TABLE ... AS SELECTwith no column list has the names and types of the query and no defaults, even when the query reads a table that has them. id INT64 GENERATED ALWAYS AS IDENTITY(START WITH 100 INCREMENT BY 10)makes an identity column: BigQuery writes its values, and each one isSTART WITHplus a multiple ofINCREMENT BY. The parentheses are required, and a table has one identity column.- An
ALWAYSidentity column takes no value from you, in anINSERT, aMERGEor anUPDATE, andNULLis a value too.GENERATED BY DEFAULTtakes the values you give and does not check them. It writes a number when you leave the column out or giveNULLorDEFAULT. - BigQuery does not promise which row gets which generated number, so a generated number is never a row number or a count.
PRIMARY KEY (col) NOT ENFORCEDin the list of aCREATE TABLEsays which columns identify a row. A key without the wordsNOT ENFORCEDfails withEnforcement of primary keys is not supported.- BigQuery plans queries with a key and can leave a join out because of it, so declare a key only on columns that are different in every 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.