Can the same column have primary key & foreign key constraint to another column

user_441664 0
Viewed 69022

Can the same column have primary key & foreign key constraint to another column?

Table1: ID - Primary column, foreign key constraint for Table2 ID
Table2: ID - Primary column, Name

Will this be an issue if i try to delete table1 data?

Delete from table1 where ID=1000;

Thanks.

foreign-keys mysql sql
4 Answers

The answer provided by Jason may have worked some time in the past but when I tried to use this answer in 2021 against a MySQL 5.7 server it complains. The syntax I used to get this working was;

CREATE TABLE a1 (
    id1 INT NOT NULL PRIMARY KEY
);
INSERT INTO a1 VALUES (1),(2),(3),(4);

CREATE TABLE a2 (
    id1 INT NOT NULL,
    PRIMARY KEY (id1),
    CONSTRAINT `fk_id1` FOREIGN KEY (id1) REFERENCES a1(id1)
);
INSERT INTO a2 VALUES (1),(2),(3);

For one-to-one relationships of this type, I would also strongly recommend you create the foreign keys as;

CONSTRAINT `fk_id1` FOREIGN KEY (id1) REFERENCES a1(id1) ON DELETE CASCADE
user_35794320
Related