#At file:///home/dlenev/src/bzr/mysql-6.1-mil8-3/
2713 Dmitry Lenev 2008-12-13
WL#148 "Foreign keys".
Milestone #8 "DML words: INSERT, UPDATE, DELETE, no EOS checks".
Work in progress. After review fixes.
Added handling of situation when SET DEFAULT clause
is applied to column without explicit default value.
modified:
mysql-test/r/foreign_key_all_engines.result
mysql-test/t/foreign_key_all_engines.test
sql/fk.cc
sql/fk_dd.cc
sql/share/errmsg.txt
=== modified file 'mysql-test/r/foreign_key_all_engines.result'
--- a/mysql-test/r/foreign_key_all_engines.result 2008-12-10 17:04:31 +0000
+++ b/mysql-test/r/foreign_key_all_engines.result 2008-12-13 16:25:15 +0000
@@ -976,6 +976,41 @@ create trigger t1_bi before insert on t1
insert into t1 values (1);
ERROR 0A000: SET FOREIGN_KEY_CHECKS is not allowed in stored function or trigger
drop table t1;
+#
+# ON UPDATE/SET DEFAULT clause and fields without explicit default values
+#
+set @old_sql_mode = @@sql_mode;
+create table t1(pk int primary key);
+insert into t1 values (0), (1), (2), (3);
+# Create table with foreign key column without an explicit default
+# value. In 'normal' mode we warn in this case.
+create table t2(fk int not null references t1(pk) on delete set default
+on update set default);
+Warnings:
+Warning 1767 Foreign key error: Constraint 'fk_t2_wxqzm': SET DEFAULT for a column 'fk' which has no DEFAULT value
+insert into t2 values (1), (2), (3);
+# UPDATE and DELETE statements will also produce warning.
+update t1 set pk= 4 where pk = 1;
+Warnings:
+Warning 1767 Foreign key error: Constraint 'fk_t2_wxqzm': SET DEFAULT for a column 'fk' which has no DEFAULT value
+delete from t1 where pk = 2;
+Warnings:
+Warning 1767 Foreign key error: Constraint 'fk_t2_wxqzm': SET DEFAULT for a column 'fk' which has no DEFAULT value
+set @@sql_mode= 'traditional';
+# In 'strict' mode attempt to set default value for field
+# without explicit default value should cause an error.
+update t1 set pk= 5 where pk = 3;
+ERROR 42000: Foreign key error: Constraint 'fk_t2_wxqzm': SET DEFAULT for a column 'fk' which has no DEFAULT value
+delete from t1 where pk = 3;
+ERROR 42000: Foreign key error: Constraint 'fk_t2_wxqzm': SET DEFAULT for a column 'fk' which has no DEFAULT value
+drop tables t1, t2;
+create table t1(pk int primary key);
+# It also makes sense to prohibit creation of such tables.
+create table t2(fk int not null references t1(pk) on delete set default
+on update set default);
+ERROR 42000: Foreign key error: Constraint 'fk_t2_m5zjv': SET DEFAULT for a column 'fk' which has no DEFAULT value
+set @@sql_mode= @old_sql_mode;
+drop tables t1;
set @@rand_seed1=10000000,@@rand_seed2=1000000;
drop tables if exists t1, t2;
create table t2 (a int primary key);
=== modified file 'mysql-test/t/foreign_key_all_engines.test'
--- a/mysql-test/t/foreign_key_all_engines.test 2008-12-10 17:04:31 +0000
+++ b/mysql-test/t/foreign_key_all_engines.test 2008-12-13 16:25:15 +0000
@@ -804,6 +804,36 @@ create trigger t1_bi before insert on t1
insert into t1 values (1);
drop table t1;
+--echo #
+--echo # ON UPDATE/SET DEFAULT clause and fields without explicit default values
+--echo #
+set @old_sql_mode = @@sql_mode;
+create table t1(pk int primary key);
+insert into t1 values (0), (1), (2), (3);
+--echo # Create table with foreign key column without an explicit default
+--echo # value. In 'normal' mode we warn in this case.
+create table t2(fk int not null references t1(pk) on delete set default
+ on update set default);
+insert into t2 values (1), (2), (3);
+--echo # UPDATE and DELETE statements will also produce warning.
+update t1 set pk= 4 where pk = 1;
+delete from t1 where pk = 2;
+set @@sql_mode= 'traditional';
+--echo # In 'strict' mode attempt to set default value for field
+--echo # without explicit default value should cause an error.
+--error ER_FK_SET_DEFAULT_NO_DEFAULT
+update t1 set pk= 5 where pk = 3;
+--error ER_FK_SET_DEFAULT_NO_DEFAULT
+delete from t1 where pk = 3;
+drop tables t1, t2;
+create table t1(pk int primary key);
+--echo # It also makes sense to prohibit creation of such tables.
+--error ER_FK_SET_DEFAULT_NO_DEFAULT
+create table t2(fk int not null references t1(pk) on delete set default
+ on update set default);
+set @@sql_mode= @old_sql_mode;
+drop tables t1;
+
#
# Test for bug #35522 "Foreign keys: 'foreign key without name' errors"
=== modified file 'sql/fk.cc'
--- a/sql/fk.cc 2008-12-12 22:06:52 +0000
+++ b/sql/fk.cc 2008-12-13 16:25:15 +0000
@@ -1036,6 +1036,37 @@ bool Foreign_key_parent_rcontext::prepar
}
}
+ if (m_ref_action == FK_OPTION_DEFAULT)
+ {
+ /*
+ Let us check that child fields to which ON ... SET DEFAULT
+ will be applied have explicit default value.
+
+ TODO: Solve problem with information hiding in views.
+ */
+ for (i= 0 ; i < m_key->key_parts; i++)
+ {
+ if (m_key->key_part[i].field->flags & NO_DEFAULT_VALUE_FLAG)
+ {
+ if (m_thd->variables.sql_mode &
+ (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES))
+ {
+ my_error(ER_FK_SET_DEFAULT_NO_DEFAULT, MYF(0), m_fk_share->name.str,
+ m_key->key_part[i].field->field_name);
+ return TRUE;
+ }
+ else
+ {
+ push_warning_printf(m_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+ ER_FK_SET_DEFAULT_NO_DEFAULT,
+ ER(ER_FK_SET_DEFAULT_NO_DEFAULT),
+ m_fk_share->name.str,
+ m_key->key_part[i].field->field_name);
+ }
+ }
+ }
+ }
+
/* Prepare structures to CASCADE into the child table. */
if (m_fk_list->action_type() == TRG_EVENT_DELETE &&
m_ref_action == FK_OPTION_CASCADE)
@@ -1244,9 +1275,6 @@ bool Foreign_key_parent_rcontext::do_cas
}
break;
case FK_OPTION_DEFAULT:
- /*
- QQ What about additional checks ? like for existence of default ?
- */
for (i= 0 ; i < m_key->key_parts; i++)
m_key->key_part[i].field->set_default();
break;
=== modified file 'sql/fk_dd.cc'
--- a/sql/fk_dd.cc 2008-12-10 13:15:01 +0000
+++ b/sql/fk_dd.cc 2008-12-13 16:25:15 +0000
@@ -1270,6 +1270,7 @@ static bool fk_column_has_dup(Key_part_s
/**
Perform checks for child and parent columns of the foreign key.
+ @param thd Thread context.
@param fkey Foreign key which columns should be checked.
@param alter_info Alter_info describing child table.
@param parent_alter_info Alter_info describing parent table.
@@ -1278,7 +1279,8 @@ static bool fk_column_has_dup(Key_part_s
@retval FALSE - Success
*/
-static bool fk_check_columns(Foreign_key_child *fkey, Alter_info *alter_info,
+static bool fk_check_columns(THD *thd, Foreign_key_child *fkey,
+ Alter_info *alter_info,
Alter_info *parent_alter_info)
{
DBUG_ENTER("fk_check_columns");
@@ -1368,6 +1370,31 @@ static bool fk_check_columns(Foreign_key
DBUG_RETURN(TRUE);
}
/*
+ Applying ON UPDATE/DELETE SET DEFAULT cascading actions to
+ columns without explicit default value is a bad idea.
+ */
+ if ((fkey->update_opt == FK_OPTION_DEFAULT ||
+ fkey->delete_opt == FK_OPTION_DEFAULT) &&
+ (field->flags & NO_DEFAULT_VALUE_FLAG))
+ {
+ if (thd->variables.sql_mode &
+ (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES))
+ {
+ /* In 'strict' mode we simply prohibit such foreign keys. */
+ my_error(ER_FK_SET_DEFAULT_NO_DEFAULT, MYF(0), fkey->name.str,
+ field->field_name);
+ DBUG_RETURN(TRUE);
+ }
+ else
+ {
+ /* And in 'normal' mode we at least warn user. */
+ push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+ ER_FK_SET_DEFAULT_NO_DEFAULT,
+ ER(ER_FK_SET_DEFAULT_NO_DEFAULT),
+ fkey->name.str, field->field_name);
+ }
+ }
+ /*
We forbid nullable parent columns in HLS of WL#148 since
they might cause unnecessary trouble. This is a non-standard
limitation which might be revised at some later point (note
@@ -1575,7 +1602,7 @@ bool fk_check_constraint_added(THD *thd,
/*
Checks for (CHILD COLUMNS) and (PARENT COLUMNS) clauses.
*/
- if (fk_check_columns(fkey, alter_info, parent_alter_info))
+ if (fk_check_columns(thd, fkey, alter_info, parent_alter_info))
DBUG_RETURN(TRUE);
if (fkey->match_opt == FK_MATCH_PARTIAL)
=== modified file 'sql/share/errmsg.txt'
--- a/sql/share/errmsg.txt 2008-12-10 13:15:01 +0000
+++ b/sql/share/errmsg.txt 2008-12-13 16:25:15 +0000
@@ -6463,6 +6463,8 @@ ER_FK_CHILD_DATA_TYPE 42000
eng "Foreign key error: Constraint '%-.192s': data type of '%s' column is illegal for foreign key"
ER_FK_SET_NULL_NOT_NULL 42000
eng "Foreign key error: Constraint '%-.192s': SET NULL for a NOT NULL column '%s'"
+ER_FK_SET_DEFAULT_NO_DEFAULT 42000
+ eng "Foreign key error: Constraint '%-.192s': SET DEFAULT for a column '%s' which has no DEFAULT value"
ER_FK_PARENT_TEMPORARY 42000
eng "Foreign key error: Constraint '%-.192s': Parent table is temporary"
ER_FK_PARENT_VIEW 42000
| Thread |
|---|
| • bzr commit into mysql-6.1-fk branch (dlenev:2713) WL#148 | Dmitry Lenev | 13 Dec |
