1

Here is my table (EMP)

mysql> select * from EMP;

enter image description here

I want to Use BETWEEN Operator in the Check statement for SAL attribute.

alter table EMP add check(SAL between 50000 and 100000);

enter image description here

I have insert a SAL value that violates the BETWEEN constraint to check whether my query works correctly or not.

insert into EMP values('2134','ARUN','TESTER','NO','1998-08-22','M',20000,2500,03); //I gave SAL as 20000 which is invalid in my case.

Instead of showing error, it accepts the invalid value!

enter image description here

How to solve this?

Kishan Lal
  • 473
  • 1
  • 5
  • 13
  • use >= and <= instead of between – Philipp Sander Jan 09 '19 at 13:46
  • MySQL 5.7 and earlier does not support check constraints, so you will have to use at trigger instead. I can't find a reference for MySQL 8; if that version supports them, then my duplicate link is partially invalid. In any case, it appears that your version does not support check constraints. – Tim Biegeleisen Jan 09 '19 at 13:46
  • @PhilippSander `SAL BETWEEN 50000 and 100000` is identical to `SAL >= 50000 AND SAL <= 100000`. – Tim Biegeleisen Jan 09 '19 at 13:47
  • 1
    According to the docs, MySQL 8 doesn't support check constraints either: `"The CHECK clause is parsed but ignored by all storage engines. See Section 1.8.2.3, “Foreign Key Differences”."` https://dev.mysql.com/doc/refman/8.0/en/create-table.html – Thorsten Kettner Jan 09 '19 at 13:49
  • I guess I better _check_ my sources before I comment next time :P – Tim Biegeleisen Jan 09 '19 at 13:54
  • Please don't post images of text. Put the text as text in the question, select it and use the `{}` button in the editor toolbar to format it as preformatted (or code). – axiac Jan 09 '19 at 14:01

0 Answers0