sql error with check constraint: error 1064(42000)

Question

CREATE TABLE credentials (
   credID SERIAL PRIMARY KEY,
   usrID INTEGER NOT NULL REFERENCES users(usrID),
   acctype CHAR(1) NOT NULL,

   CONSTRAINT acc_constraint
   CHECK (acctype = '4' OR acctype = '8' OR acctype = 'C' OR acctype = 'B'),
   cardnum VARCHAR(16),
   pinnum VARCHAR(8),
   credactive BOOLEAN NOT NULL DEFAULT true,
   created TIMESTAMP DEFAULT current_timestamp,
   CONSTRAINT cardset 
       CHECK ( ((acctype = 'C' OR acctype = 'B') AND 
        cardnum IS NOT NULL AND CHARACTER_LENGTH(cardnum) = 16) OR
       ((acctype = '4' OR acctype = '8') AND cardnum IS NULL) ),
   CONSTRAINT pinset
       CHECK ( (acctype = 'C' AND pinnum IS NULL) OR
               ((acctype = 'B' OR acctype = '4') AND
            pinnum IS NOT NULL AND CHARACTER_LENGTH(pinnum) = 4) OR
           (acctype = '8' AND pinnum IS NOT NULL AND 
    CHARACTER_LENGTH(pinnum) = 8) )
  );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( credID SERIAL PRIMARY KEY, usrID INTEGER NOT NULL REFERENCES use' at line 1

You know that check constraints can be defined in MySQL but have absolutely no effect, right? — juergen d, Jun 04 '14 at 17:16

score 1 · Answer 1 · edited May 23 '17 at 12:11

Your column definition comes first, then your constraint definitions, you can't place a constraint definition in the middle of your columns. If you move:

CONSTRAINT acc_constraint
CHECK (acctype = '4' OR acctype = '8' OR acctype = 'C' OR acctype = 'B'),

to after

created TIMESTAMP DEFAULT current_timestamp,

Then your table will create just fine:

CREATE TABLE credentials (
    credID SERIAL PRIMARY KEY,
    usrID INTEGER NOT NULL REFERENCES users(usrID),
    acctype CHAR(1) NOT NULL,

    CONSTRAINT acc_constraint
    CHECK (acctype = '4' OR acctype = '8' OR acctype = 'C' OR acctype = 'B'),
    cardnum VARCHAR(16),
    pinnum VARCHAR(8),
    credactive BOOLEAN NOT NULL DEFAULT true,
    created TIMESTAMP DEFAULT current_timestamp,
    CONSTRAINT cardset 
        CHECK ( ((acctype = 'C' OR acctype = 'B') AND 
        cardnum IS NOT NULL AND CHARACTER_LENGTH(cardnum) = 16) OR
        ((acctype = '4' OR acctype = '8') AND cardnum IS NULL) ),
    CONSTRAINT pinset
        CHECK ( (acctype = 'C' AND pinnum IS NULL) OR
                ((acctype = 'B' OR acctype = '4') AND
            pinnum IS NOT NULL AND CHARACTER_LENGTH(pinnum) = 4) OR
            (acctype = '8' AND pinnum IS NOT NULL AND 
    CHARACTER_LENGTH(pinnum) = 8) )
    );

Example on SQL Fiddle

This is however a relatively futile exercise since although you can define check constraints in MySQL they are not enforced

sql error with check constraint: error 1064(42000)

1 Answers1