Support `Foreign Key` constraints

[shenli]

Feature Request

Is your feature request related to a problem? Please describe:

No

Describe the feature you'd like:

Most of RDBMS support Foreign Key and a lot of applications rely on this feature (such as Zabbix).

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

[morgo]

I think a lot of users coming from MySQL think of FOREIGN KEY constraints as adding overhead. They can be really useful at providing join elimination. Consider the following:

DROP TABLE IF EXISTS t1, t2;

CREATE TABLE t1 (
 id INT NOT NULL PRIMARY KEY auto_increment,
 a CHAR(32) NOT NULL
);

CREATE TABLE t2 (
 id INT NOT NULL PRIMARY KEY auto_increment,
 t1_id INT NOT NULL,
 b CHAR(32) NOT NULL,
 FOREIGN KEY (t1_id) REFERENCES t1(id)
);

INSERT INTO t1 VALUES (1, 'aa');
INSERT INTO t2 VALUES (1, 1, 'bbb');

Currently TiDB treats the first two statements as equal, but with FOREIGN KEY constraints the third can be treated as equal as well:

EXPLAIN SELECT * FROM t2;
EXPLAIN SELECT t2.* FROM t2 LEFT JOIN t1 ON t2.t1_id = t1.id;
EXPLAIN SELECT t2.* FROM t2 INNER JOIN t1 ON t2.t1_id = t1.id;

[rnav1234]

Also consider adding foreign keys as a good way to ensure cascade delete's happen when needed

[Anutrix]

What's the status on this? Is anyone working on this?

[dveeden]

What's the status on this? Is anyone working on this?

Hello @Anutrix, This is still something that we would like to implement, but is also a complex feature to implement. I don't know where this is on our roadmap currently. Is there a specific issue for which you need foreign keys?

[Anutrix]

Not a specific issue but we were thinking to migrate from one of our old large DB to a modern distributed DB system at some point.
That project's owner/current maintainer said that it uses foreign keys for stuff like ON DELETE CASCADE. I'm assuming there's more to it than that too since my team just started scanning that project.
We know there's not much options for high MySQL compatibility among NewSQLs but we're still trying to make sure we left no options so that DB migration would need as less project modification as possible.

[crazycs520]

I will work on this now.

[aidendou]

hi @crazycs520 thx for taking this. could you share some rough timeline on this feature?

[SamuelMarks]

Looks like this issue can be closed:

Starting from v6.6.0, TiDB supports foreign keys and foreign key constraints. Foreign keys allow cross-referencing related data across tables, and foreign key constraints can ensure the consistency of related data.

From https://docs.pingcap.com/zh/tidb/v6.6/foreign-key with Google Translate (I can't read Chinese!)

Good work team

[dveeden]

Looks like this issue can be closed:

Starting from v6.6.0, TiDB supports foreign keys and foreign key constraints. Foreign keys allow cross-referencing related data across tables, and foreign key constraints can ensure the consistency of related data.

From https://docs.pingcap.com/zh/tidb/v6.6/foreign-key with Google Translate (I can't read Chinese!)

Good work team

The information is available in English on https://docs.pingcap.com/tidb/v6.6/foreign-key

[shenli]

Thanks for the team! We can close this issue now.