Wildcard character in CHECK in Create table taken as string

Question

I created an info table that stores employee record, the SQL query for it as follows...

CREATE TABLE info1 (empid VARCcHAR(8) PRIMARY KEY CONSTRAINT empchk CHECK (empid IN ('kh\%' ESCAPE '\''), initials CHAR(6), fname CHAR(25) NOT NULL, lname CHAR(25), userstatus INTEGER NOT NULL, designation CHAR(10) NOT NULL);

Now, as you can see constraint in empid is 'kh%' where AFAIR % means any number of characters that follow (limited to 8 of course) can be anything right?

Am using Java DB and strangely it has taken the % symbol also to be a part of the string so if I enter 'khce0001' (no quotes of course) it says empchk voilation, it only takes in 'kh%'

What do I do? Why is this happening?

Answer 1

The mistake in this SQL query is that I have used 'IN' instead of 'LIKE' (which I believe does wildcard checking), so I dropped the constraint with...

ALTER TABLE info DROP CONSTRAINT empchk;

and altered the table with...

ALTER TABLE info ADD CONSTRAINT empchk CHECK (empid LIKE ('kh%'))

and hence the correct SQL Query should have been...

CREATE TABLE info1 (empid VARCHAR(8) PRIMARY KEY CONSTRAINT empchk CHECK (empid LIKE ('kh%')), initials CHAR(6), fname CHAR(25) NOT NULL, lname CHAR(25), userstatus INTEGER NOT NULL, designation CHAR(20) NOT NULL);