CODEWITHSUNDEEP
sqlsql-servernullconstraintsalter
dxlachx
dxlachx

Reputation: 1

Modify SQL column field to be nullable, if I added constraint do I need to remove constraint as well?

I altered a table with the following script

ALTER TABLE TABLENAME ADD [flagField] CHAR(1) DEFAULT 'N' NOT NULL;
ALTER TABLE TABLENAME ADD CONSTRAINT XCK6_tablename CHECK([flagField] in ('Y', 'N'));

If I want to reverse this script to change that field in the table to allow a nullable state, do I need to remove the constraint before making the field nullable?

so if I run 

ALTER TABLE TABLENAME ALTER COLUMN fieldFlag CHAR(1) NULL

will that be fine as is or should I also remove the constraint?

Upvotes: 0

Views: 223

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You only have to make the field NULLable.

The logic for constraints differs from the logic for WHERE and CASE WHEN conditions. For WHERE and CASE WHEN, NULL results are treated the same as false.

CHECK is instead validating the data. It accepts as valid anything that is not explicitly false. So, there is no need to include NULL checking in the constraint.

If you did, the correct logic would be:

CHECK (flagField in ('Y', 'N') or flagField is null)

Here is a db<>fiddle illustrating that the behavior is as described above.

Upvotes: 1

Related Questions