Declare all columns in a database to be NOT NULL by default?

Question

Rather than littering my schema with NOT NULL almost everywhere I'd rather declare somewhere that all columns should be NOT NULL unless explicitly 'CAN BE NULL' or something.

Is there some way to do this? I'm using SQLite but curious for PostgreSQL also

No, by default a column can be null. afaik no database allows the default to be not null. — Stu
– Stu, Commented Apr 18, 2022 at 19:38
If your schema is so large you want this 1) consider whether the schema could be smaller and 2) consider using a schema builder rather than raw SQL. — Schwern
– Schwern, Commented Apr 18, 2022 at 19:43
I would guess that the SQL Standard (ISO/IEC 9075-1:2016) requires columns to be nullable by default, but I do not want to pay 178 Swiss Franc to read the spec :) — Jared Beck
– Jared Beck, Commented Apr 18, 2022 at 19:47
Does this answer your question? How to set all table columns to NOT NULL at once? — Tony Arra
– Tony Arra, Commented Apr 18, 2022 at 20:13
I don't think dynamic SQL to automtically amend each column definition is what the OP had in mind - besides that answer doesn't take into account that the script will fail if any rows exist containing NULL. — Stu
– Stu, Commented Apr 18, 2022 at 20:57

Andy Lester · Accepted Answer · 2022-04-18 20:03:41Z

1

No, there are no databases that I know of that allow this. It would certainly be non-standard if they did.

answered Apr 18, 2022 at 20:03

Andy Lester

94.2k16 gold badges106 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Philip Kelley · Accepted Answer · 2022-04-18 20:23:07Z

1

One of the dumbest things I ever saw in Microsoft SQL Server (yes, I know) were the two commands:

Not one, no, two separate commands. They read in part "Modifies the behavior of the session to override default nullability of new columns when the ANSI null default option for the database is false."

After finding these, I figured I could never assume anything about how SQL handled column default NULLability, and now I always set NULL/NOT NULL myself, no matter what RDBMS I use. (Back then I assumed this was an ANSII standard, and now I'm scarred for life.)

answered Apr 18, 2022 at 20:23

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

Collectives™ on Stack Overflow

Declare all columns in a database to be NOT NULL by default?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related