CODEWITHSUNDEEP
mysqlforeign-keyscomposite-primary-key
user2172296
user2172296

Reputation: 3

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.

Upvotes: 0

Views: 4814

Answers (1)

xlecoustillier
xlecoustillier

Reputation: 16351

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.

Upvotes: 3

Related Questions