Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

Home Forums How do you get a double-column unique key as foreign key? – MindStick
Ask Question

1 Answers

Aryan Kumar
Aryan Kumar 01 Aug 2023
Report Abuse

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.
MindStick Training
Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.

Open .md