How to set default values properly in SQL?

Question

I have a table which should not have any NULL values at all. When I set a NOT NULL constraint, it disallows the statement and it fails with the constraint error. Default constraint will take place only if the column is not referenced in the insert statement.

How can we get around this? If a insert statement has a NULL value for any of the columns, then the DEFAULT value must be taken instead of the NULL values.

create table temp1 (col0 int, col1 int default 0);
insert into temp1 (col0) values (1); --> col0 -> 1 and col1 ->0
insert into temp1 (col0,col1) values (1,NULL); --> col0 -> 1 and col1 -> NULL (I would expect a 0 here instead of NULL)
alter table temp1 (add column col2 int not null default 0); --> col2 -> 0 for all the existing rows
insert into temp1 (col0) values (2);  --> col0 -> 2 and col1 ->0 and col2-> 0
select * from temp1;

COL0 |COL1   |COL2
1    |0      |0   
1    |(null) |0   
2    |0      |0

An Insert Update trigger might help u...... – Dennis Feb 02 '17 at 06:43 — Dennis, Feb 02 '17 at 06:43

score 2 · Accepted Answer · edited May 23 '17 at 11:53

Converting NULL into a column's default value for an insert is not part of standard SQL.

As you observed, you can omit the column from the insert statement but that is not the same as inserting a NULL value. Rather, in effect, the default value for a column's DEFAULT is NULL (SQL92 13.8 General Rules 4b); this is why inserting default values gives NULLs if there is no explicit default defined.

You can alternatively include the column and use the keyword DEFAULT (SQL92 7.1 General Rules 2). WX2 doesn't currently support this syntax but Kognitio plans to add it in the upcoming version 8.2.

insert into temp1 (col0, col1) values (1, DEFAULT);

The standard only allows you to use DEFAULT as shown above and not in a compound expression or in an insert-select statement.

-- NOT VALID IN STANDARD SQL!
insert into temp1 (col0, col1) values (1, case when ... then 1 else DEFAULT end);

-- NOT VALID IN STANDARD SQL!
insert into temp1 (col0, col1) select C1, DEFAULT from ...;

You can solve this using a COALESCE() function.

insert into temp1 (col0, col1) select E1, COALESCE(E2, 0) from ...;

Other databases don't in general allow converting NULLs to default values either: see similar questions for SQL Server, MySQL, Postgres and Firebird. Oracle does have a non-standard syntax to create table columns with DEFAULT ON NULL which would do what you want.

(Kognitio might add DEFAULT in compound expressions or DEFAULT ON NULL as extensions in a future release.)

This is exemplary.. I can't do a +2 so I do a +1 and accept :) — Srini V, Feb 06 '17 at 04:45

How to set default values properly in SQL?

1 Answers1