2

i created a data base 'test' with table table 'user', inside the table there are 'id' and 'name' columns

CREATE TABLE `test`.`user` (`id` INT NOT NULL AUTO_INCREMENT , `name` TEXT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

now i want to add a constraint so no digits allowed in the 'name' and i don't know how to do it. i'm pretty sure i need to add 'name' NOT LIKE '%[^0-9]%' but i just can't get it right

i tried ALTER TABLE `test`.`user` ADD CONSTRAINT no_digit_check CHECK ('name' NOT LIKE '%[^0-9]%'); but it gives me an error above 'CHECK' says a new statement was found but no delimiter

thanks

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
aditya shaikh
  • 21
  • 1
  • what does `select version();` show? do you have a `;` at the end of the previous statement (or the proper delimiter if you've used the DELIMITER command)? – ysth May 19 '22 at 15:40
  • @ysth it says 10.4.24-MariaDB. i have a ```;``` in the end, and the delimiter is set to be the same – aditya shaikh May 19 '22 at 15:43
  • your alter table seems to be correct: https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=15d354640cc4a6c35726ad7668d424e7 so the problem would seem to be in something before that – ysth May 19 '22 at 15:44
  • @ysth this is the only thing i did, created a table with the said command in the top and altered it with the next command – aditya shaikh May 19 '22 at 15:47
  • what client are you using? I suspect whatever it is is trying to parse your sql and doesn't know about the newer syntax. – ysth May 19 '22 at 16:17
  • @ysth i am using exampp and phpmyadmin, as suggested by my teacher – aditya shaikh May 20 '22 at 09:23
  • I see sometimes upgrading phpmyadmin is recommended when you get this error; I don't know if it will help in this case. If it doesn't, I suggest just using the mysql command line client. – ysth May 20 '22 at 14:51

1 Answers1

0

Use backtics, not apostrophes:

CHECK ( `name` ...)

instead of

CHECK ( 'name' ...)
Rick James
  • 135,179
  • 13
  • 127
  • 222