Constraints, Sql Server

Language: Layout:

CREATE TABLE Session (
	[SessionId] [int] NOT NULL,
	[Title] [nchar](50) NULL,
	[SpeakerID] [int] NULL,
	[SessionState] [nchar](40) NULL
);

INSERT Session ([SessionId], [Title], [SpeakerID], [SessionState]) VALUES (1, NULL, NULL, N'0')
INSERT Session ([SessionId], [Title], [SpeakerID], [SessionState]) VALUES (2, NULL, NULL, N'test')
INSERT Session ([SessionId], [Title], [SpeakerID], [SessionState]) VALUES (3, NULL, NULL, N'Useless')
INSERT Session ([SessionId], [Title], [SpeakerID], [SessionState]) VALUES (4, NULL, NULL, NULL)
INSERT Session ([SessionId], [Title], [SpeakerID], [SessionState]) VALUES (5, NULL, NULL, NULL)

Select * from Session;

---- You cannot Alter because you have null values in your column
--Alter Table Session
--ALTER COLUMN SessionState  nchar(40) NOT NULL;

---- You cannot also add constraint when you have values like 'test' and '0'.
---- It conflict with the constraint
--Alter Table Session
--ADD CONSTRAINT Constraint_name Check (SessionState IN ('Useful', 'Useless', 'Unknown'));

---- get all values that are not supported
Select * from [Session] WHERE SessionState IS NULL OR SessionState NOT IN ('Useful', 'Useless', 'Unknown');

---- change all of them
UPDATE [Session] SET SessionState='Unknown' WHERE SessionState IS NULL;
UPDATE [Session] SET SessionState='Unknown' WHERE SessionState NOT IN ('Useful', 'Useless', 'Unknown');

--Select * from Session; -- To see the result

---- Now you can run these queries
Alter Table Session
ALTER COLUMN SessionState nchar(40) NOT NULL;

Alter Table Session
ADD CONSTRAINT Constraint_name Check (SessionState IN ('Useful', 'Useless', 'Unknown'));

---- Final Result
Select * from Session;

View schema
Execution time: 0,03 sec, rows selected: 14, rows affected: 9, absolute service time: 0,17 sec