Question 197678 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Constraint check 'something' is violated

I am struggling with a problem and I hope someone could help a brother out.

I tried INSERT INTO but got an error message: Error Code: 3819. Check constraint ‘inventory_chk_1’ is violated.

To troubleshoot the issue, i want to examine the specific check constraint named "inventory_chk_1" to understand the condition it enforces and I tried this:

SELECT constraint_name, check_clause
FROM information_schema.check_constraints
WHERE constraint_schema = 'book_shop'
  AND table_name = 'inventory'
  AND constraint_name = 'inventory_chk_1';

I get yet another error: Unknown column ‘table_name’ in ‘where clause’.
Then i tried this:

SELECT constraint_name, check_clause
FROM information_schema.check_constraints
WHERE constraint_schema = 'book_shop'
  AND table_name = 'inventory'
  AND constraint_type = 'CHECK'
  AND constraint_name = 'inventory_chk_1';

and so on and on..

Can someone point what my mistake might be?

Much appreciated!

I tried multiple modifications of the code but none worked. I want to solve the issue!

Tags:

2

Answers


  1. 0

    Take a look at the docs for the Information_schema.Check_Constraints table. I don’t know about you, but I don’t see any Table_Name column. Instead, constraint names must be unique across an entire schema. And since you’re already looking up the constraint by schema, you can simply remove the table_name predicate completely and still have confidence you’re seeing the right result.

    Login or Signup to reply.
  2. 0

    You can do:

    select cast(check_clause as char(1000)) as check_clause
from information_schema.check_constraints 
where constraint_name = 'chk1';

    For example:

    create table t100 (a int, b int, c int,
  constraint chk1 check (a + b * c < 1000)
);

    Result:

    CHECK_CLAUSE
--------------------------------------------------------
((`a` + (`b` * `c`)) < 1000)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search