The NOT NULL Constraint in BigQuery

Are you using the NOT NULL constraint in BigQuery? This constraint is a staple when working with databases.

So, when creating a table, you have the option to declare if a particular column is required and should not be null. We do that by simply adding the NOT NULL to the column declaration.

You also can check it on an existing table by checking the column (field) MODE.

It can have 3 values:
- NULLABLE (which is the default if you've haven't set anything)
- REQUIRED (if you've set NOT NULL for this columns)
- REPEATED (for ARRAYS).

Upon trying insert or update data that will violated this constraint, the statement will fail.

Overall, setting the right MODE for your field helps enforce your expectations about the data that should be inserted or updated in a particular table.

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.