0

IS it possible that a not nullable column can be setup to either a) add value freely with INSERT/UPDATE query b) if value not provided on insert, to avoid null entry added default the value to that of another column?

I know that with a computed column I can set the default to be another columns value, but I cannot then override this.

2
  • Does a default constraint not work for you? Mar 31 '21 at 11:02
  • @Charlieface no, because a default doesn't allow you to default to another field's value, it is just a static value.
    – Sami.C
    Apr 1 '21 at 6:46
0

An example:

CREATE TABLE test (id INT,
                   val INT);
GO
CREATE TRIGGER tr
ON test
AFTER INSERT, UPDATE
AS
UPDATE test
SET val = 123     -- custom value which must be set instead of NULL
                  -- maybe any expression 
                  -- and even an output of the subquery
WHERE EXISTS ( SELECT NULL
               FROM INSERTED
               WHERE id = test.id
                 AND val IS NULL );
GO
INSERT INTO test VALUES (1,1), (2,null), (3,3);
SELECT * FROM test;
GO
id | val
-: | --:
 1 |   1
 2 | 123      -- NULL is replaced with custom value
 3 |   3
UPDATE test SET val = NULL where id = 1;
SELECT * FROM test;
GO
id | val
-: | --:
 1 | 123      -- NULL is replaced with custom value
 2 | 123
 3 |   3

db<>fiddle here

0

While a Trigger would work probably just fine like in Akina's answer, I'm with Charlieface on using a Default Constraint since this is exactly the kind of problem it was meant to solve, and is slightly lighter of a solution. It also lives as part of the table definition, instead of being a separate object on the server that needs to be maintained, so if you were to ever script your tables, the Default Constraint would automatically script with it, as opposed to a Trigger.

Example straight from Microsoft:

CREATE TABLE dbo.doc_exz 
(
      column_a INT,
      column_b INT DEFAULT 50
);

The above will allow you to freely INSERT and UPDATE column_b but if you don't specify a value for column_b when you INSERT into the table, then it'll default to a value of 50.

And here's the same example as above, but with specifying a name for the Default Constraint:

CREATE TABLE dbo.doc_exz 
(
     column_a INT,
     column_b INT CONSTRAINT DF_Doc_Exz_Column_B DEFAULT 50
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.