Home > Software engineering >  How to rewrite mysql constraints syntax?
How to rewrite mysql constraints syntax?

Time:02-17

I need to add a check constraint on mysql using phpmyadmin

I have a table wp_nrOrevePerPunetore with 3 colums ID , start_date and end_date

this works fine

alter table wp_nrOrevePerPunetore add constraint conEnddtbiggerThanStartDt check(end_date < '2030-01-01' );

but this

alter table wp_nrOrevePerPunetore add constraint conEnddtbiggerThanStartDtt 
check(end_date > start_date );

throws this error:

Static analysis: 1 errors were found during analysis.

A new statement was found, but no delimiter between it and the previous one. (near "check" at position 79)

What would be the right syntax for the second constraint to get executed?

ps: both start_date and end_date are datetime type

CodePudding user response:

phpMyAdmin attempts to do syntax validation of your SQL statements, but it has fallen behind and does not recognize valid syntax in newer versions of MySQL. I viewed the changelog for phpMyAdmin, but I don't see any mention that they have added support for check constraints.

So this is not a problem with MySQL Server, it's a problem with phpMyAdmin. See this open feature request: https://github.com/phpmyadmin/phpmyadmin/issues/13592

It's pretty disappointing that MySQL has supported CHECK constraints since 8.0.16 (2019-04-25), but phpMyAdmin hasn't included support for that syntax. But phpMyAdmin is an open-source project with only two individual programmers doing most of the work, so progress is slow.

One comment on that issue report says:

Right now, you can add the CHECK constraint with a custom SQL statement. Even though the parser indicates a syntax error, it works.

You can also apply check constraints if you use the command-line MySQL client, not phpMyAdmin.

  • Related