[Bug]: different table has same foreign key , mo execute succeed but mysql reported duplicate error

[heni02]

Is there an existing issue for the same bug?

• [X] I have checked the existing issues.

Environment

- Version or commit-id (e.g. v0.1.0 or 8b23a93):f81502b5691ef13b003c61aa8a322b9862343db3
- Hardware parameters:
- OS type:
- Others:

Actual Behavior

table nation and supplier has same foreign key name ck, mo execute succeed but mysql reported duplicate error mysql:

mo :

Expected Behavior

No response

Steps to Reproduce

CREATE TABLE region_fk( R_REGIONKEY INTEGER NOT NULL, R_NAME CHAR(25) NOT NULL, R_COMMENT VARCHAR(152), PRIMARY KEY (R_REGIONKEY) );

insert into region_fk values(2,"ASIA","ges. thinly even pinto beans ca");

CREATE TABLE NATION_fk( N_NATIONKEY INTEGER NOT NULL, N_NAME CHAR(25) NOT NULL, N_REGIONKEY INTEGER NOT NULL, N_COMMENT VARCHAR(152), PRIMARY KEY (N_NATIONKEY),constraint fk foreign key(N_REGIONKEY) REFERENCES region_fk(R_REGIONKEY) ); insert into nation_fk values(13,"VIETNAM",2,"hely enticingly express accounts. even, final");

CREATE TABLE supplier_fk( S_SUPPKEY INTEGER NOT NULL, S_NAME CHAR(25) NOT NULL, S_ADDRESS VARCHAR(40) NOT NULL, S_NATIONKEY INTEGER NOT NULL, S_PHONE CHAR(15) NOT NULL, S_ACCTBAL DECIMAL(15,2) NOT NULL, S_COMMENT VARCHAR(101) NOT NULL, PRIMARY KEY (S_SUPPKEY),constraint fk foreign key(S_NATIONKEY) REFERENCES nation_fk(N_NATIONKEY) ); insert into supplier_fk values(9991,"Supplier#000009991","RnP1Z uvwftshFtf",13,"23-451-948-8464",6785.10,". furiously pending accounts b");

Additional information

No response

[heni02]

@ouyuanning plz confirm this issue

[ouyuanning]

that's a feature. we need save constraint to meta table

[domingozhang]

constraint meta table will be implemented in future

[qingxinhome]

association #10172

[qingxinhome]

经过对mysql的调研发现，在mysql中，同一个schema下不能存在两个相同的外键约束名，即使这两个外键约束属于不同的表。 @heni02 @daviszhen @sukki37 @fengttt @ouyuanning

验证用例如下： create database db3; use db3;

create table t1( a int primary key );

create table t2( a2 int primary key, constraint zk foreign key(a2) REFERENCES t1(a) );

create table t3( a3 int primary key, constraint zk foreign key(a3) REFERENCES t1(a) ); ERROR 1826 (HY000): Duplicate foreign key constraint name 'zk'

---

create database db4; use db4;

create table t3( a3 int primary key, constraint zk foreign key(a3) REFERENCES db3.t1(a) );

create table t4( a4 int primary key, constraint zk foreign key(a4) REFERENCES db3.t1(a) ); ERROR 1826 (HY000): Duplicate foreign key constraint name 'zk'

[daviszhen]

@heni02 我们的逻辑做不到跟mysql 完全一样。不支持。