Avoid inserting a particular value into sql table

Question

I am not sure, if this can be achieved in SQL.

INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, 999);

If the user tries to insert 999 in column3, convert it to null or avoid inserting it.

I am trying to find if I can find any solution from SQL Server side.

Mark Kram · Accepted Answer · 2019-12-04 19:27:10Z

1

How about using a Check Constraint:

ALTER TABLE table_name ADD CHECK (column3<>999);

answered Dec 4, 2019 at 19:27

Mark Kram

5,8527 gold badges56 silver badges73 bronze badges

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

2 Comments

mr_gemini Over a year ago

instead of terminating the statement, can we convert to null- @Mark Kram

Sean Lange Over a year ago

You would have to use instead of triggers to convert 999 to a NULL. You would need both insert and update triggers to accomplish this. I would not recommend this approach though as I strongly dislike the system changing data that is being inserted.

Dale K · Accepted Answer · 2019-12-04 19:33:39Z

You cannot case the 999 in the insert statement. It really depends on the implementation. Is this strictly in the Database? Is it being called with a java/python/scala library? In the database, I would recommend the following:

set @value1 = val1; set @value2 = val2; set @value3 = if(val3=999,NULL,val3); INSERT INTO table_name (column1, column2, column3) VALUES (@value1, @value2, @value3);

If outside the Database, just use the same logic.

Collectives™ on Stack Overflow

Avoid inserting a particular value into sql table

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related