How to check a cast from nvarchar to varbinary in SQL Server-CodePudding

I have an old database named OldData with data that I want to pass to my new database named School. I have a problem with my check on my database creating script. The column in question from old database is of type nvarchar and I want to cast it to varbinary.

My column creation:

studentFamSize VARBINARY(MAX) NOT NULL DEFAULT 0

I want to check if my varbinary column is N'LE3' or N'GT3', my check currently is like this:

CONSTRAINT CHK_studentFamSize 
    CHECK (studentFamSize = 0x4C4533 OR studentFamSize = 0x475433)

And my cast:

CAST(famsize AS VARBINARY(100))

I get an error:

The INSERT statement conflicted with the CHECK constraint "CHK_studentFamSize". The conflict occurred in database "School", table "dbo.Student", column 'studentFamSize'.

If someone can help me, I'd appreciate it.

CodePudding user response：

The solution here is simple, fix your design:

ALTER TABLE dbo.YourTable ALTER COLUMN studentFamSize nvarchar(3) NOT NULL;

Then add the correct CONSTRAINT:

ALTER TABLE dbo.YourTable
ADD CONSTRAINT CHK_studentFamSize CHECK (studentFamSize IN (N'LE3', N'GT3'));