I recommend using checklists for schema changes like this.
Nullable but not null
31–40 of 49 posts
Re: Nullable but not null
#32If you don't care for old data having null , you could add a check contraint with nocheck (this is sql server fwiw)
for example
create table foo(id int) insert foo values (1), (2), (3)
insert foo values (null)
select * from foo
id
1
2
3
NULL
ALTER TABLE foo with nocheck ADD CONSTRAINT CheckNotnull check (id IS NOT NULL)
insert foo values (null)
Msg 547, Level 16, State 0, Line 13 The INSERT statement conflicted with the CHECK constraint "CheckNotnull". The conflict occurred in database tempdb", table "dbo.foo", column 'id'. The statement has been terminated.
However be aware that if you update an existing value to NULL, you will still get the error
update foo set id = null where id = 2
Msg 547, Level 16, State 0, Line 20 The UPDATE statement conflicted with the CHECK constraint "CheckNotnull". The conflict occurred in database "tempdb", table "dbo.foo", column 'id'.
Re: Nullable but not null
#33Am I missing something here in my (MS) SQL world? if a new field is added as null, I do that to the (now) 20 year old system to we don't break 100's of stored procs - any (new) code that needs that field, has to check for it being null...
in sql server, you can simply add a not null check constraint with nocheck (see my comment with full code)
ALTER TABLE foo WITH NOCHECK ADD CONSTRAINT CheckNotnull CHECK (id IS NOT NULL)
any new values coming in cannot be null but the values already in the table with null are fine... then you can update them to not null over time
Re: Nullable but not null
#34Earlier quoted context omitted.
I believe PostgreSQL does this since v11, which was released in 2018: (current is v17) > Many other useful performance improvements, including the ability to avoid a table rewrite for ALTER TABLE ... ADD COLUMN with a non-null column default https://www.postgresql.org/docs/release/11.0/ I think there is some restriction there, like the default can't be "volatile" - I can't remember the precise definition here but I t…
That is correct, for non-volatile default values Postgres is quick, which means that it is generally a safe operation. Also interesting, `now()` is non-volatile because it's defined as "start of the transaction". So if you add a column with `DEFAULT now()` all rows will get the same value. But `timeofday()` is not volatile, so `DEFAULT timeofday()` is going to lock the table for a long time. A bit of a subtle gotcha.
Thanks for the info. One minor point:
> But `timeofday()` is not volatile, so `DEFAULT timeofday()` is going to lock the table for a long time.
Perhaps the “not” was a typo?
Re: Nullable but not null
#35[dead]
Re: Nullable but not null
#36I've seen worse. Some teams use JSON for their data. Not only each field can be missing (aka NULL), it can also be "null". Or a different type. I envy your team who's only mistake is to forget setting NULLABLE. Rainbows and unicorns ;)
What if almost everything is NULLABLE? including the supposedly primary key and foreign keys of the table? I've had the firsthand experience building a consumer for that kind of DB and it's hell to get anything running correctly without literally writing a dedicated layer to sanity check everything.
> without literally writing a dedicated layer to sanity check everything
It's not attractive to developers for a variety of reasons, but encapsulation is generally cheaper (in every sense) than the alternatives by orders of magnitude. If a system is hard to alter and other people rely on it to do their jobs, thinking about it as set in stone during my planning process will save me grief and heartache.
Starting with an independent client in mind makes it easier to plan around things like reconstructing tables from composed exports or dealing with authoritatively incorrect data. It's a good collection point for hard-coded edge case handling, and a great location for all of your discovery notes.
Re: Nullable but not null
#37I've seen worse. Some teams use JSON for their data. Not only each field can be missing (aka NULL), it can also be "null". Or a different type. I envy your team who's only mistake is to forget setting NULLABLE. Rainbows and unicorns ;)
No shame on JSON.. XML can do this too!
Re: Nullable but not null
#38I've seen worse. Some teams use JSON for their data. Not only each field can be missing (aka NULL), it can also be "null". Or a different type. I envy your team who's only mistake is to forget setting NULLABLE. Rainbows and unicorns ;)
Worked in an enterprise project where they use a BPMN engine for all business logic (designed by non-technical people).
Each process can either have predefined fields (take time to implement) or key-string value store (yes, they chose this).
Either the BPMN logic or some JSs (some, as not all people can code) in PBMN nodes or some Java hooks (a lot, wrote under those other people requests) edit those values.
So when something's wrong, we swam in the sea of key-value, search all the code for who update this field, and why it's value is not JSON nor null nor boolean but a string "Not used, waiting for policy 1234".
After that project, I'm happy every time I can use a DB to store data, more so if it can run in local, much more so if it's relational.
Re: Nullable but not null
#39This is interesting! A field being nullable because it's legitimately optional in the domain model is one thing, but for new fields which shouldn't be nullable in the domain model, unless you can pick a reasonable identity value, you need a concept of absence that's different from null. Luckily the intersection of "non-nullable fields" and "fields with no reasonable identity value" and "fields which didnt exist in v1…
> you need a concept of absence that's different from null
Could you give an example? Null _is_ absence, the way I see it
Re: Nullable but not null
#40From my experience, any new field that gets added to existing tables (after a non-trivial system got deployed to production environment with actual clients) must always be nullable.
Firstly, it’s more practical and brings fewer surprises to all people involved (especially if there are many people involved).
Secondly, if we’re being pedantic, it is a mere acknowledgement of the fact that the field was not there before and things worked without it, and now the field is here, so in all senses the field _is actually_ optional, so all the touching code should know how to handle this.