How do you get a double-column unique key as foreign key?

Question

Aryan Kumar · Answer

To get a double column unique key as foreign key, you can use the following SQL statement:
SQL
ALTER TABLE child_table
ADD CONSTRAINT foreign_key_constraint
FOREIGN KEY (column_1, column_2)
REFERENCES parent_table (column_1, column_2);
For example, if you have the following two tables:
SQL
CREATE TABLE languages (
  language_id INT NOT NULL AUTO_INCREMENT,
  iso_639_1 CHAR(2) NOT NULL,
  main_flag VARCHAR(20) NOT NULL,
  PRIMARY KEY (language_id),
  UNIQUE KEY languages_un (iso_639_1, main_flag)
);
CREATE TABLE tests (
  test_id INT NOT NULL AUTO_INCREMENT,
  test_name VARCHAR(50) NOT NULL,
  iso_639_1 CHAR(2) NOT NULL,
  main_flag VARCHAR(20) NOT NULL,
  PRIMARY KEY (test_id),
  FOREIGN KEY (iso_639_1, main_flag) REFERENCES languages (iso_639_1, main_flag)
);
Then you can use the following SQL statement to create a foreign key constraint on the
iso_639_1 and main_flag columns in the tests table:
SQL
ALTER TABLE tests
ADD CONSTRAINT test_language_fk
FOREIGN KEY (iso_639_1, main_flag)
REFERENCES languages (iso_639_1, main_flag);
This will ensure that the iso_639_1 and main_flag columns in the
tests table reference a valid row in the languages table.
Here are some things to keep in mind when creating a foreign key constraint on a double column unique key:
The two columns in the child table must be the same data type as the two columns in the parent table.The two columns in the child table must be unique in the child table.The two columns in the parent table must be unique in the parent table.

forum

How do you get a double-column unique key as foreign key?

Utpal Vishwas

Can you answer this question?

1 Answers

Liked By