summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/main/check_constraint.result52
-rw-r--r--mysql-test/main/check_constraint.test50
-rw-r--r--mysql-test/main/constraints.result2
-rw-r--r--mysql-test/main/constraints.test2
-rw-r--r--mysql-test/main/type_json.result2
-rw-r--r--mysql-test/main/type_json.test2
-rw-r--r--sql/share/errmsg-utf8.txt2
-rw-r--r--sql/table.cc6
8 files changed, 103 insertions, 15 deletions
diff --git a/mysql-test/main/check_constraint.result b/mysql-test/main/check_constraint.result
index 9a32e6f12bc..df7222e301a 100644
--- a/mysql-test/main/check_constraint.result
+++ b/mysql-test/main/check_constraint.result
@@ -10,9 +10,9 @@ t1 CREATE TABLE `t1` (
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (100,100);
insert into t1 values (1,1);
-ERROR 23000: CONSTRAINT `a` failed for `test`.`t1`
+ERROR HY000: FIELD CONSTRAINT `a` failed for `test`.`t1`
insert into t1 values (20,1);
-ERROR 23000: CONSTRAINT `b` failed for `test`.`t1`
+ERROR HY000: FIELD CONSTRAINT `b` failed for `test`.`t1`
insert into t1 values (20,30);
ERROR 23000: CONSTRAINT `min` failed for `test`.`t1`
insert into t1 values (500,500);
@@ -60,7 +60,7 @@ t1 CREATE TABLE `t1` (
CONSTRAINT `CONSTRAINT_1` CHECK (`a` + `b` + `c` < 500)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values(105,105,105);
-ERROR 23000: CONSTRAINT `c` failed for `test`.`t1`
+ERROR HY000: FIELD CONSTRAINT `c` failed for `test`.`t1`
insert into t1 values(249,249,9);
ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1`
insert into t1 values(105,105,9);
@@ -145,7 +145,7 @@ ERROR HY000: Function or expression '@b' cannot be used in the CHECK clause of `
create table t1 (a int check (a = 1));
insert t1 values (1);
insert t1 values (2);
-ERROR 23000: CONSTRAINT `a` failed for `test`.`t1`
+ERROR HY000: FIELD CONSTRAINT `a` failed for `test`.`t1`
insert t1 values (NULL);
select * from t1;
a
@@ -165,13 +165,13 @@ ERROR 22007: Truncated incorrect DOUBLE value: 'Ken'
SHOW WARNINGS;
Level Code Message
Error 1292 Truncated incorrect DOUBLE value: 'Ken'
-Error 4025 CONSTRAINT `FirstName` failed for `test`.`t1`
+Error 4144 FIELD CONSTRAINT `FirstName` failed for `test`.`t1`
INSERT INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian');
ERROR 22007: Truncated incorrect DOUBLE value: 'Ken'
SHOW WARNINGS;
Level Code Message
Error 1292 Truncated incorrect DOUBLE value: 'Ken'
-Error 4025 CONSTRAINT `FirstName` failed for `test`.`t1`
+Error 4144 FIELD CONSTRAINT `FirstName` failed for `test`.`t1`
INSERT IGNORE INTO t1 VALUES (NULL, 'Ken');
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'Ken'
@@ -197,3 +197,43 @@ EmployeeID FirstName
5 Ken
6 Brian
drop table t1;
+#
+# Test same name for field/table check constraints
+#
+create table t(a int, b int check(b>0),
+constraint b check(a<b), constraint a check(a>0),
+constraint x check(a>10), constraint x check(a>10));
+ERROR HY000: Duplicate CHECK constraint name 'x'
+create table t(a int, b int check(b>0),
+constraint b check(a<b), constraint a check(a>0),
+constraint x check(a>10));
+show create table t;
+Table Create Table
+t CREATE TABLE `t` (
+ `a` int(11) DEFAULT NULL,
+ `b` int(11) DEFAULT NULL CHECK (`b` > 0),
+ CONSTRAINT `b` CHECK (`a` < `b`),
+ CONSTRAINT `a` CHECK (`a` > 0),
+ CONSTRAINT `x` CHECK (`a` > 10)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+# Field constraint 'b' will fail
+insert into t values (-1, 0);
+ERROR HY000: FIELD CONSTRAINT `b` failed for `test`.`t`
+# Table constraint 'b' will fail
+insert into t values (1,1);
+ERROR 23000: CONSTRAINT `b` failed for `test`.`t`
+alter table t drop constraint b;
+alter table t add constraint b check(a<b);
+show create table t;
+Table Create Table
+t CREATE TABLE `t` (
+ `a` int(11) DEFAULT NULL,
+ `b` int(11) DEFAULT NULL CHECK (`b` > 0),
+ CONSTRAINT `a` CHECK (`a` > 0),
+ CONSTRAINT `x` CHECK (`a` > 10),
+ CONSTRAINT `b` CHECK (`a` < `b`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+# Table constraint 'x' will fail
+insert into t values (1,1);
+ERROR 23000: CONSTRAINT `x` failed for `test`.`t`
+drop table t;
diff --git a/mysql-test/main/check_constraint.test b/mysql-test/main/check_constraint.test
index 02081071bd4..91dc2f7c9cc 100644
--- a/mysql-test/main/check_constraint.test
+++ b/mysql-test/main/check_constraint.test
@@ -6,9 +6,9 @@ set @save_check_constraint=@@check_constraint_checks;
create table t1 (a int check(a>10), b int check (b > 20), constraint `min` check (a+b > 100), constraint `max` check (a+b <500)) engine=myisam;
show create table t1;
insert into t1 values (100,100);
---error ER_CONSTRAINT_FAILED
+--error ER_FIELD_CONSTRAINT_FAILED
insert into t1 values (1,1);
---error ER_CONSTRAINT_FAILED
+--error ER_FIELD_CONSTRAINT_FAILED
insert into t1 values (20,1);
--error ER_CONSTRAINT_FAILED
insert into t1 values (20,30);
@@ -37,7 +37,7 @@ alter table t1 add check (a+b+c < 500);
set check_constraint_checks=@save_check_constraint;
show create table t1;
---error ER_CONSTRAINT_FAILED
+--error ER_FIELD_CONSTRAINT_FAILED
insert into t1 values(105,105,105);
--error ER_CONSTRAINT_FAILED
insert into t1 values(249,249,9);
@@ -98,7 +98,7 @@ create table t1 (a int check (a > @b));
#
create table t1 (a int check (a = 1));
insert t1 values (1);
---error ER_CONSTRAINT_FAILED
+--error ER_FIELD_CONSTRAINT_FAILED
insert t1 values (2);
insert t1 values (NULL);
select * from t1;
@@ -135,3 +135,45 @@ INSERT INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian');
set sql_mode=default;
select * from t1;
drop table t1;
+#
+# There should be difference between errors when using the same name for field
+# and table check constraints.
+#
+--echo #
+--echo # Test same name for field/table check constraints
+--echo #
+# There is a field constraint called 'b'
+# Table constraint called 'a' and 'b' and 2 constraints called 'x'
+# An error will be generated because of the duplicating 'x' constraint
+--error ER_DUP_CONSTRAINT_NAME
+create table t(a int, b int check(b>0),
+ constraint b check(a<b), constraint a check(a>0),
+ constraint x check(a>10), constraint x check(a>10));
+
+create table t(a int, b int check(b>0),
+ constraint b check(a<b), constraint a check(a>0),
+ constraint x check(a>10));
+
+show create table t;
+
+# Generate error when field constraint 'b' is violated
+--echo # Field constraint 'b' will fail
+--error ER_FIELD_CONSTRAINT_FAILED
+insert into t values (-1, 0);
+
+# Generate error when table constraint 'b' is violated.
+# From example below it can be seen also that 'x' constraint will be violated
+# as well but first one is 'b' constraint.
+--echo # Table constraint 'b' will fail
+--error ER_CONSTRAINT_FAILED
+insert into t values (1,1);
+
+# In order to test thesis from above
+alter table t drop constraint b; # table contraint will be dropped
+alter table t add constraint b check(a<b);
+show create table t;
+
+--echo # Table constraint 'x' will fail
+--error ER_CONSTRAINT_FAILED
+insert into t values (1,1);
+drop table t;
diff --git a/mysql-test/main/constraints.result b/mysql-test/main/constraints.result
index fe9398ea8ce..d891c10a2bd 100644
--- a/mysql-test/main/constraints.result
+++ b/mysql-test/main/constraints.result
@@ -7,7 +7,7 @@ t1 CREATE TABLE `t1` (
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1);
insert into t1 values (0);
-ERROR 23000: CONSTRAINT `a` failed for `test`.`t1`
+ERROR HY000: FIELD CONSTRAINT `a` failed for `test`.`t1`
drop table t1;
create table t1 (a int, b int, check (a>b));
show create table t1;
diff --git a/mysql-test/main/constraints.test b/mysql-test/main/constraints.test
index 1997c23bfa9..33cff30f8a8 100644
--- a/mysql-test/main/constraints.test
+++ b/mysql-test/main/constraints.test
@@ -8,7 +8,7 @@ drop table if exists t1;
create table t1 (a int check (a>0));
show create table t1;
insert into t1 values (1);
---error ER_CONSTRAINT_FAILED
+--error ER_FIELD_CONSTRAINT_FAILED
insert into t1 values (0);
drop table t1;
create table t1 (a int, b int, check (a>b));
diff --git a/mysql-test/main/type_json.result b/mysql-test/main/type_json.result
index 2c1fdbe2b95..94e73097588 100644
--- a/mysql-test/main/type_json.result
+++ b/mysql-test/main/type_json.result
@@ -20,7 +20,7 @@ t1 CREATE TABLE `t1` (
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert t1 values ('[]');
insert t1 values ('a');
-ERROR 23000: CONSTRAINT `a` failed for `test`.`t1`
+ERROR HY000: FIELD CONSTRAINT `a` failed for `test`.`t1`
set timestamp=unix_timestamp('2010:11:12 13:14:15');
create or replace table t1(a json default(json_object('now', now())));
show create table t1;
diff --git a/mysql-test/main/type_json.test b/mysql-test/main/type_json.test
index 0cff9366145..eea8d44985d 100644
--- a/mysql-test/main/type_json.test
+++ b/mysql-test/main/type_json.test
@@ -14,7 +14,7 @@ show create table t1;
create or replace table t1(a json not null check (json_valid(a)));
show create table t1;
insert t1 values ('[]');
---error ER_CONSTRAINT_FAILED
+--error ER_FIELD_CONSTRAINT_FAILED
insert t1 values ('a');
set timestamp=unix_timestamp('2010:11:12 13:14:15');
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 77347472521..42c5505d607 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7919,3 +7919,5 @@ ER_VERS_QUERY_IN_PARTITION
eng "SYSTEM_TIME partitions in table %`s does not support historical query"
ER_KEY_DOESNT_SUPPORT
eng "%s index %`s does not support this operation"
+ER_FIELD_CONSTRAINT_FAILED
+ eng "FIELD CONSTRAINT %`s failed for %`-.192s.%`-.192s"
diff --git a/sql/table.cc b/sql/table.cc
index ced8f8c0ae8..8b33ccd8675 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -5243,7 +5243,11 @@ int TABLE::verify_constraints(bool ignore_failure)
if (((*chk)->expr->val_int() == 0 && !(*chk)->expr->null_value) ||
in_use->is_error())
{
- my_error(ER_CONSTRAINT_FAILED,
+ enum_vcol_info_type vcol_type= (*chk)->get_vcol_type();
+ DBUG_ASSERT(vcol_type == VCOL_CHECK_TABLE ||
+ vcol_type == VCOL_CHECK_FIELD);
+ my_error(vcol_type == VCOL_CHECK_TABLE ? ER_CONSTRAINT_FAILED :
+ ER_FIELD_CONSTRAINT_FAILED,
MYF(ignore_failure ? ME_JUST_WARNING : 0), (*chk)->name.str,
s->db.str, s->table_name.str);
return ignore_failure ? VIEW_CHECK_SKIP : VIEW_CHECK_ERROR;
View Lorry Controller Status