mirror of
https://github.com/MariaDB/server.git
synced 2025-07-19 17:58:24 +02:00
cffbb17480
Before MySQL 4.0.18, user-specified constraint names were ignored. Starting with MySQL 4.0.18, the specified constraint name was prepended with the schema name and '/'. Now we are transforming into a format where the constraint name is prepended with the dict_table_t::name and the impossible UTF-8 sequence 0xff. Generated constraint names will be ASCII decimal numbers. On upgrade, old FOREIGN KEY constraint names will be displayed without any schema name prefix. They will be updated to the new format on DDL operations. dict_foreign_t::sql_id(): Return the SQL constraint name without any schemaname/tablename\377 or schemaname/ prefix. row_rename_table_for_mysql(), dict_table_rename_in_cache(): Simplify the logic: Just rename constraints to the new format. dict_table_get_foreign_id(): Replaces dict_table_get_highest_foreign_id(). innobase_get_foreign_key_info(): Let my_error() refer to erroneous anonymous constraints as "(null)". row_delete_constraint(): Try to drop all 3 constraint name variants. Reviewed by: Thirunarayanan Balathandayuthapani Tested by: Matthias Leich
108 lines
4.6 KiB
Text
108 lines
4.6 KiB
Text
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
|
|
CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id, id),
|
|
FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (t1_id) REFERENCES t1(id) ON UPDATE CASCADE) ENGINE=INNODB;
|
|
CREATE TABLE t3 (id INT PRIMARY KEY, t2_id INT, INDEX par_ind (t2_id),
|
|
FOREIGN KEY (id, t2_id) REFERENCES t2(t1_id, id) ON DELETE CASCADE) ENGINE=INNODB;
|
|
select * from information_schema.TABLE_CONSTRAINTS where
|
|
TABLE_SCHEMA= "test";
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
|
|
def test 1 test t2 FOREIGN KEY
|
|
def test 1 test t3 FOREIGN KEY
|
|
def test 2 test t2 FOREIGN KEY
|
|
def test PRIMARY test t1 PRIMARY KEY
|
|
def test PRIMARY test t2 PRIMARY KEY
|
|
def test PRIMARY test t3 PRIMARY KEY
|
|
select * from information_schema.KEY_COLUMN_USAGE where
|
|
TABLE_SCHEMA= "test";
|
|
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
|
|
def test 1 def test t2 t1_id 1 1 test t1 id
|
|
def test 1 def test t3 id 1 1 test t2 t1_id
|
|
def test 1 def test t3 t2_id 2 2 test t2 id
|
|
def test 2 def test t2 t1_id 1 1 test t1 id
|
|
def test PRIMARY def test t1 id 1 NULL NULL NULL NULL
|
|
def test PRIMARY def test t2 id 1 NULL NULL NULL NULL
|
|
def test PRIMARY def test t3 id 1 NULL NULL NULL NULL
|
|
drop table t3, t2, t1;
|
|
CREATE TABLE t1(a1 INT NOT NULL, a2 INT NOT NULL,
|
|
PRIMARY KEY(a1, a2)) ENGINE=INNODB;
|
|
CREATE TABLE t2(b1 INT, b2 INT, INDEX (b1, b2),
|
|
CONSTRAINT A1
|
|
FOREIGN KEY (b1, b2) REFERENCES t1(a1, a2)
|
|
ON UPDATE CASCADE ON DELETE NO ACTION) ENGINE=INNODB;
|
|
CREATE TABLE t3(b1 INT, b2 INT, INDEX t3_indx (b1, b2),
|
|
CONSTRAINT A2
|
|
FOREIGN KEY (b1, b2) REFERENCES t2(b1, b2)
|
|
ON UPDATE SET NULL ON DELETE RESTRICT) ENGINE=INNODB;
|
|
CREATE TABLE t4(b1 INT, b2 INT, UNIQUE KEY t4_ukey (b1, b2),
|
|
CONSTRAINT A3
|
|
FOREIGN KEY (b1, b2) REFERENCES t3(b1, b2)
|
|
ON UPDATE NO ACTION ON DELETE SET NULL) ENGINE=INNODB;
|
|
CREATE TABLE t5(b1 INT, b2 INT, INDEX (b1, b2),
|
|
CONSTRAINT A4
|
|
FOREIGN KEY (b1, b2) REFERENCES t4(b1, b2)
|
|
ON UPDATE RESTRICT ON DELETE CASCADE) ENGINE=INNODB;
|
|
select a.CONSTRAINT_SCHEMA, b.TABLE_NAME, CONSTRAINT_TYPE,
|
|
b.CONSTRAINT_NAME, UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME,
|
|
MATCH_OPTION, UPDATE_RULE, DELETE_RULE, b.REFERENCED_TABLE_NAME
|
|
from information_schema.TABLE_CONSTRAINTS a,
|
|
information_schema.REFERENTIAL_CONSTRAINTS b
|
|
where a.CONSTRAINT_SCHEMA = 'test' and a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and
|
|
a.CONSTRAINT_NAME = b.CONSTRAINT_NAME;
|
|
CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE REFERENCED_TABLE_NAME
|
|
test t2 FOREIGN KEY A1 test PRIMARY NONE CASCADE NO ACTION t1
|
|
test t3 FOREIGN KEY A2 test b1 NONE SET NULL RESTRICT t2
|
|
test t4 FOREIGN KEY A3 test t3_indx NONE NO ACTION SET NULL t3
|
|
test t5 FOREIGN KEY A4 test t4_ukey NONE RESTRICT CASCADE t4
|
|
drop tables t5, t4, t3, t2, t1;
|
|
create database `db-1`;
|
|
use `db-1`;
|
|
create table `t-2` (
|
|
id int(10) unsigned not null auto_increment,
|
|
primary key (id)
|
|
) engine=innodb;
|
|
create table `t-1` (
|
|
id int(10) unsigned not null auto_increment,
|
|
idtype int(10) unsigned not null,
|
|
primary key (id),
|
|
key fk_t1_1 (idtype),
|
|
constraint fk_t1_1 foreign key (idtype) references `t-2` (id)
|
|
) engine=innodb;
|
|
use test;
|
|
select referenced_table_schema, referenced_table_name
|
|
from information_schema.key_column_usage
|
|
where constraint_schema = 'db-1'
|
|
order by referenced_table_schema, referenced_table_name;
|
|
referenced_table_schema referenced_table_name
|
|
NULL NULL
|
|
NULL NULL
|
|
db-1 t-2
|
|
drop database `db-1`;
|
|
create table t1(id int primary key) engine = Innodb;
|
|
create table t2(pid int, foreign key (pid) references t1(id)) engine = Innodb;
|
|
set foreign_key_checks = 0;
|
|
drop table t1;
|
|
select UNIQUE_CONSTRAINT_NAME
|
|
from information_schema.referential_constraints
|
|
where constraint_schema = schema();
|
|
UNIQUE_CONSTRAINT_NAME
|
|
NULL
|
|
drop table t2;
|
|
set foreign_key_checks = 1;
|
|
#
|
|
# Bug#55973 Assertion `thd->transaction.stmt.is_empty()'
|
|
# on CREATE TABLE .. SELECT I_S.PART
|
|
#
|
|
DROP TABLE IF EXISTS t1;
|
|
DROP VIEW IF EXISTS v1;
|
|
CREATE VIEW v1 AS SELECT 1;
|
|
CREATE TABLE t1 engine = InnoDB AS
|
|
SELECT * FROM information_schema.partitions
|
|
WHERE table_schema= 'test' AND table_name= 'v1';
|
|
DROP TABLE t1;
|
|
DROP VIEW v1;
|
|
CREATE TABLE t1(i int) ENGINE=Innodb ROW_FORMAT=REDUNDANT DATA DIRECTORY='MYSQLTEST_VARDIR/tmp';
|
|
SELECT CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME='t1';
|
|
CREATE_OPTIONS
|
|
row_format=REDUNDANT DATA DIRECTORY='MYSQLTEST_VARDIR/tmp/'
|
|
DROP TABLE t1;
|