One of Composite primary key as Foreign key Mysql

问题:

I Have two Tables.

CREATE TABLE One(
    Oneid int,
    Twoid int,
    data char(20),
    PRIMARY KEY(Oneid,Twoid) )

Table One is Oneid and Twoid as primary key.

CREATE TABLE Two(
    Twoid int,
    data char(20),
    PRIMARY KEY(Twoid) )

And I want to One.Twoid is foreign key for Table Two. How to solve it. Thank a lot.

回答1:

Add the constraint in the CREATE TABLE statement:

CREATE TABLE Two(
    Twoid int,
    data char(20),
    PRIMARY KEY (Twoid));

CREATE TABLE One(
    Oneid int,
    Twoid int,
    data char(20),
    PRIMARY KEY (Oneid,Twoid),
    FOREIGN KEY (Twoid) REFERENCES Two(Twoid)); -- <== here

See fiddle.

Or use ALTER TABLE if your tables already exist:

ALTER TABLE One
ADD CONSTRAINT FK_Twoid FOREIGN KEY (Twoid) REFERENCES Two (Twoid);

See fiddle.