Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support Foreign Key constraints #18209

Closed
shenli opened this issue Jun 24, 2020 · 10 comments
Closed

Support Foreign Key constraints #18209

shenli opened this issue Jun 24, 2020 · 10 comments
Assignees
Labels
feature/accepted This feature request is accepted by product managers help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. type/feature-request Categorizes issue or PR as related to a new feature.

Comments

@shenli
Copy link
Member

shenli commented Jun 24, 2020

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:

@shenli shenli added the type/feature-request Categorizes issue or PR as related to a new feature. label Jun 24, 2020
@scsldb scsldb added the feature/reviewing This feature request is reviewing by product managers label Jul 16, 2020
@scsldb scsldb added feature/accepted This feature request is accepted by product managers help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. and removed feature/reviewing This feature request is reviewing by product managers labels Jul 21, 2020
@zz-jason zz-jason removed their assignment Jul 21, 2020
@morgo
Copy link
Contributor

morgo commented Dec 21, 2020

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
Copy link

rnav1234 commented Apr 6, 2021

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

@Anutrix
Copy link

Anutrix commented May 21, 2022

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

@dveeden
Copy link
Contributor

dveeden commented May 24, 2022

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
Copy link

Anutrix commented May 24, 2022

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
Copy link
Contributor

I will work on this now.

@crazycs520 crazycs520 self-assigned this Jul 11, 2022
@aidendou
Copy link
Contributor

aidendou commented Aug 1, 2022

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

@SamuelMarks
Copy link

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
Copy link
Contributor

dveeden commented Mar 3, 2023

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
Copy link
Member Author

shenli commented Mar 3, 2023

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

@shenli shenli closed this as completed Mar 3, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
feature/accepted This feature request is accepted by product managers help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. type/feature-request Categorizes issue or PR as related to a new feature.
Projects
None yet
Development

No branches or pull requests

10 participants