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.

Answer 1

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.