Changing the column from NULL to NOT NULL when some data is inserted

Question

I have a column Col1 nvarchar(10) null

I have to write a check constraint or trigger (I think it's not possible with check constraint), that will change the Col1 from null to not null when and only when some data is entered into the field or, rather, it will deny the column to get a null value after some non-null value is entered into the field.

It's because of application that first checks if that field is null, and if it is then it adds some value to it. After that the field can not be changed back to null.

For now I have the following:

  create trigger [TRG_Col1_NotNull] on my.Table
      instead of update
          as
              begin
                  if exists (
                      select * from inserted as i
                      where i.Col1 is null
                      )
                  raiserror ('You can not change the value of Col1 to null', 16, 1)
                  rollback transaction
              end

Is this the best (or even correct) way to do this or is there any better and easier solution for this (maybe check constraint somehow)?

OK! The update! Application works like this: It first save data to table in PK column, Col1, Col2, Col3 values 1, null, text, date. After that it checks if Col1 is null and reads the PK column and writes it's values to Col1. So I get the 1, 1, text, data.

Hello. I think there might be some kind of concept error: what about the case when several records have Col1 NULL and then some new records are inserted with Col1 != NULL? In this case the existing NULL values would not match your expectations... should they automatically be updated? Or do you simply want to prevent the insertion of new NULL values? Furthermore: would it be possible for you to provide some example data / table structure / ...? — Tyron78, Aug 28 '17 at 09:29
You need to write ALTER TABLE statement. https://stackoverflow.com/questions/689746/altering-a-column-null-to-not-null — Amit Kumar Singh, Aug 28 '17 at 09:33
There can't be null values for that column. it's always some value that application give. So their is no concept error. I simply want to prevent insertion of null values but after a not null values is inserted. @Amit : I can not have a not null constraint by default in table, because application first checks if this is null field, and only then sends a value to be inserted in that field. I need after that to prevent entering back to null value. — IvanVC, Aug 28 '17 at 11:01

etsa · Accepted Answer · 2017-08-28T10:09:04.977

0

This could do what you asked (I know: it's after UPDATE, so actually, you'll change values two times, but I will not use AFTER/INSTEAD: what if other values should be updated?).

CREATE TABLE TES1 (ID INT, COL1 VARCHAR(10));
INSERT INTO TES1 VALUES (1,'X');
INSERT INTO TES1 VALUES (2,NULL);


CREATE TRIGGER TRG1 ON TES1
 AFTER UPDATE
 AS
 BEGIN
     UPDATE A SET COL1=CASE WHEN d.COL1 IS NULL THEN i.COL1 ELSE d.COL1 END
     FROM TES1 A
     INNER JOIN DELETED d ON  A.ID = d.ID
     INNER JOIN INSERTED i ON A.ID = i.ID;
  END

Sample data

    UPDATE TES1 SET COL1 = NULL WHERE ID=1;
SELECT * FROM TES1;
    UPDATE TES1 SET COL1 = 'A' WHERE ID=1;
SELECT * FROM TES1;
    UPDATE TES1 SET COL1 = 'B' WHERE ID=2;
SELECT * FROM TES1;
    UPDATE TES1 SET COL1 = 'C' WHERE ID=2;
SELECT * FROM TES1;

edited Aug 28 '17 at 10:09

answered Aug 28 '17 at 09:36

etsa

5,020
1
7
18

Would this allow to first time application writes a null value to the col1? If I understand if I have null value and delete it (d.col1 is null) and then write null (i.col1 is null), then it writes null (i.col1) because of 'when d.col1 is null then i.col1'? Am I right? – IvanVC Aug 30 '17 at 07:21
No, "deleted" is a conventional way for MSSQL trigger to indicate records containing column values before update, while "inserted" are the new values. You can find a lot of info on microsoft site, looking for trigger. Anyway, can do all tests you want with script in my answer and verify results. – etsa Aug 31 '17 at 06:43
I little bit modified your answer and it does the trick. Thank you. – IvanVC Sep 05 '17 at 07:27

score 0 · Answer 2 · answered Aug 28 '17 at 12:08

0

You can create a CHECK constraint that will work only for new values.

ALTER TABLE [dbo].[Test] WITH NOCHECK ADD CONSTRAINT [CK_Test] CHECK (([Col1] IS NOT NULL))
GO

ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [CK_Test]
GO

WITH NOCHECK option means that constraint will be created successfully even if the table has NULL values.

But, after this constraint is created, an attempt to insert new NULL value or update existing value to NULL will fail.

answered Aug 28 '17 at 12:08

Vladimir Baranov

31,799
5
53
90

This is cool but will it allow me to first write null value, and after that a not null value?! – IvanVC Aug 30 '17 at 07:13
@IvanVC, no this CHECK constraint will work for all new values. It will not allow to insert a new NULL value. After reading your detailed explanations of the required logic I think that most likely you need a trigger to implement the logic you described. – Vladimir Baranov Aug 30 '17 at 12:09
Thank you for information. I was first thinking about something like this constraint but wasn't sure if it will do the job. But thank you anyway. – IvanVC Aug 30 '17 at 13:23

Changing the column from NULL to NOT NULL when some data is inserted

2 Answers2