0

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.

João Ramos
  • 3
  • 5
  • 2
    The error is telling you the problem; a value already in the table doesn't meet the requirements of the `CONSTRAINT` so the `CONSTRAINT` can't be created. – Thom A Nov 26 '21 at 12:58
  • @Larnu but I have tried this "SELECT * FROM studentBD2017 WHERE famsize not in ('LE3', 'GT3');" and I got 0 results... – João Ramos Nov 26 '21 at 12:59
  • `'LE3'` is a `varchar` not an `nvarchar`. In binary, `'LE3'` and `N'LE3'` are completely different values. – Thom A Nov 26 '21 at 13:00
  • @Larnu it is nvarchar in my old database – João Ramos Nov 26 '21 at 13:01
  • 2
    Why are you storing 3 character strings as varbinary and why max? – Martin Smith Nov 26 '21 at 13:01
  • Yes, and `0x4C4533` as an **`nvarchar`** is not `'LE3'`, it's `N'䕌3'`. – Thom A Nov 26 '21 at 13:02
  • @MartinSmith I can switch that later, that's not my main problem, and its school exercise, my teacher wants binary type – João Ramos Nov 26 '21 at 13:03
  • So, should I compare to N'䕌3' in my check? – João Ramos Nov 26 '21 at 13:03
  • 2
    No, if you inserted the value `N'LE3'` into your column, then the value will be `0x4C0045003300`. Again, from a binary perspective `'LE3'` and `N'LE3'` are *very* different values (`0x4C4533` and `0x4C0045003300` respectively). – Thom A Nov 26 '21 at 13:04
  • 2
    @JoãoRamos - on the contrary this is entirely the main problem. If your target column was a suitable datatype and you weren't doing an incorrect binary comparison in the check constraint you wouldn't have this issue – Martin Smith Nov 26 '21 at 13:05
  • @Larnu Yes, in my oldData there are only 'LE3' and 'GT3' values, but as nvarchar – João Ramos Nov 26 '21 at 13:06
  • @Larnu its 'LE3' not N'LE3' where did you get that from? – João Ramos Nov 26 '21 at 13:07
  • No, it's an `nvarchar`, it's `N'LE3'`. `'LE3'` is a **`varchar`**. See [What is the meaning of the prefix N in T-SQL statements and when should I use it?](https://stackoverflow.com/questions/10025032/what-is-the-meaning-of-the-prefix-n-in-t-sql-statements-and-when-should-i-use-it) – Thom A Nov 26 '21 at 13:07
  • @Larnu Now I understand but the datatype in my old database is nvarchar, not varchar – João Ramos Nov 26 '21 at 13:11
  • 2
    As @MartinSmith highlighted, this is a problem entirely of your own making. Yes `'LE3'` and `N'LE3'` will be see as equal in a comparison expression, because `'LE3'` would be implicitly converted to an `nvarchar` due to [Data type precedence](https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver15). On the other hand the `varbinary` representations of those 2 values (again `0x4C4533` and `0x4C0045003300` respectively) would **not** be equal because they clearly are not. – Thom A Nov 26 '21 at 13:13
  • *"Now I understand but the datatype in my old database is nvarchar, not varchar "* Then, with respect, you clearly don't. `0x4C4533 <> N'LE3'`. `0x4C4533 = 'LE3'`. – Thom A Nov 26 '21 at 13:13
  • @Larnu so, LE3=0x4C4533 and GT3=0x4C0045003300 ?? – João Ramos Nov 26 '21 at 13:14
  • No... You aren't reading the comments here. I don't know what else to say here other than you seem to think that `varchar` and `nvarchar` values are the same, they aren't... *Especially* when dealing with binary values. Fix your design, fix the problem. I'm out. – Thom A Nov 26 '21 at 13:17
  • @Larnu I'm reading the comments, you are basically saying that my datatype from my old database is wrong and it should be varchar instead of nvarchar right? but i cant do nothing related to that – João Ramos Nov 26 '21 at 13:23
  • Nope, I didn't say that at all. – Thom A Nov 26 '21 at 13:25
  • @Larnu So I didn't understand what you said... – João Ramos Nov 26 '21 at 13:28

1 Answers1

2

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'));
Thom A
  • 88,727
  • 11
  • 45
  • 75