Constraint check 'something' is violated

Question

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!

If you are using the MySQL client you could try `show create table `. — The Impaler, Jun 30 '23 at 14:55
Does this answer your question? [Show constraints on tables command](https://stackoverflow.com/questions/4004205/show-constraints-on-tables-command) — gbjbaanb, Jun 30 '23 at 15:01
so there's no column in check_constraints called table_name. So remove it, you only need "constraint_name='inventory_chk_1', there shoulnd't be more than 1. — gbjbaanb, Jun 30 '23 at 15:01

score 0 · Answer 1 · answered Jun 30 '23 at 14:56

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.

The Impaler · Answer 2 · 2023-06-30T15:11:57.920

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)

edited Jun 30 '23 at 15:11

answered Jun 30 '23 at 15:05

The Impaler

45,731
9
39
76

Constraint check 'something' is violated

2 Answers2