Composite foreign key is it possible?

Question

I have a tables:

CREATE TABLE test_part1 ( 

  Prefix ENUM('A','B') NOT NULL, 
  Unique_number int(11) NOT NULL, 
  Data_set1 decimal(5,2), 
  PRIMARY KEY (Prefix, Unique_number)
  ) ;

CREATE TABLE test_part2 ( 
  composite_foregin_key tinytxt(12) NOT NULL, 
  Data1 varchar (11), 
  Data2 varchar (11) 
  ) ;

How can I reference composite key from “test_part1” in table “test_part2” as the foreign key?

Do I need to create 2 columns called fkPrefix and fkUnique_number and use the same data types or can I create one column called composite_foregin_key?

If option with composite_foregin_key is possible, do I need to change data type of composite key to Prefix txt (1) and Unique_ number txt (11) as the composite_foregin_key would be txt (12)?

It is very important to me to join Prefix and Unique_number together as item under Unique_number is stored in either condition A or B and when referenced Prefix and Unique_ number are always together.

I am trying to learn as much as possible about computer science but it’s not my field of expertise and before working on this project I was involved in basic db architecture really long time ago. Please be understanding if my question doesn’t make any sense. I will try to phrase it other way if needed.

"phrase" not "fraze" :-). Please can you re-format your question to make it easily readable instead of one big block of text. — ADyson, Jul 17 '17 at 14:27
aww :) my dyslexia is coming out already, thanks for correcting me ADyson — Moonlit, Jul 17 '17 at 15:15
TL;DR but composite foreign keys are indeed possible. You may want to share some full `CREATE TABLE` statements. — Álvaro González, Jul 17 '17 at 15:21
https://stackoverflow.com/questions/10565846/use-composite-primary-key-as-foreign-key might help — ADyson, Jul 19 '17 at 11:12

score 1 · Accepted Answer · answered Jul 19 '17 at 15:22

The syntax itself is rather straightforward:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Just note that [] means "optional", | means "one of" and , ... means you can add more similar items separated by commas—you don't actually type those characters.

The column types of the linked columns need to match—that's true both single-column foreign keys as well. Since your test_part1's PK is composed by these two columns:

Prefix ENUM('A','B') NOT NULL, 
Unique_number int(11) NOT NULL,

... I'd expect to see two similarly typed columns in test_part2 but it isn't the case. Your composite_foregin_key candidate is TINYTEXT and is only one column.

Following these guidelines:

CREATE TABLE test_part1 ( 
    Prefix ENUM('A','B') NOT NULL, 
    Unique_number int(11) NOT NULL, 
    Data_set1 decimal(5,2),

    PRIMARY KEY (Prefix, Unique_number)
);

CREATE TABLE test_part2 ( 
    composite_foregin_key_1 ENUM('A','B') NOT NULL, 
    composite_foregin_key_2 int(11) NOT NULL, 
    Data1 varchar (11), 
    Data2 varchar (11),

    CONSTRAINT test_part2_fk1 FOREIGN KEY (composite_foregin_key_1, composite_foregin_key_2)
    REFERENCES test_part1 (Prefix, Unique_number)
);

It's also not a bad idea to use meaningful names, even (or specially) when learning.

Thank you! I have amended my table and created foreign keys. — Moonlit, Jul 20 '17 at 14:56

Composite foreign key is it possible?

1 Answers1