Can an existing blank field be changed to that it defaults to Y, but can only be changed to N

Question

I am a complete novice, I hope my terminology is correct. I have a database created long ago. It helps keep track of visitors.

The visitor’s data is entered manually, but some fields are calculated and most cannot be empty. There is a field (“group2”) which is unused. I want to start using it. I want it to be pre-filled in by default with “Y”,but be able to change it to “N” (and no other characters and not empty).

In my research, it looks like the ENUM function is usable to limit input; I wonder if I am on the right track, and if so, how do you set the pre-filled default to Y?

Answer 1

CREATE TABLE test (id INT IDENTITY (1,1), val VARCHAR(255));

-- alter column datatype
ALTER TABLE test
  ALTER COLUMN val CHAR(1) NOT NULL;
-- add default value and value restriction
ALTER TABLE test
  ADD DEFAULT 'Y' FOR val,
      CONSTRAINT check_in_YN CHECK (val IN ('Y','N'));

INSERT INTO test VALUES (DEFAULT); -- default value

INSERT INTO test VALUES ('Y'); -- explicit value

INSERT INTO test VALUES ('N'); -- explicit value

INSERT INTO test VALUES ('X'); -- non-allowed value, fail

Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the CHECK constraint "check_in_YN". The conflict occurred in database "fiddle_3f2b380640a04eb1808095ee1945dd3f", table "dbo.test", column 'val'.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

SELECT * FROM test;

id	val
1	Y
2	Y
3	N

fiddle