Using DATE as part of a composite foreign key in MySQL

I have a table that, minimally, has

CREATE TABLE IF NOT EXISTS T (
    A int(11) NOT NULL,
    B date NOT NULL
);

A and B themselves happen to have FK constraints later on, but since this isn't part of the MWE I don't include them.

I then have another table:

CREATE TABLE IF NOT EXISTS U (
    C int(11) NOT NULL,
    D date NOT NULL,
    FOREIGN KEY (C) REFERENCES T(A),
    FOREIGN KEY (D) REFERENCES T(B)
);

But I get

#1005 - Can't create table `DBName`.`u` (errno: 150 "Foreign key constraint is incorrectly formed")

I realised that the problem is actually with the date constraint. I read that this cannot be done because T(B) is not either unique or primary. However, this shouldn't be a big issue because I also have another foreign key T(A) that should be unique.

However, I also read that this is only an issue in MySQL, and that InnoDB has an extension that supports it if, for any reason, you need it. I tried adding ENGINE=InnoDB at the end, but this did not change anything.

Can this be done, and if not, what's the best practice to have a foreign key of (int(11), date)?

Further details:

Server: 127.0.0.1 via TCP/IP
Server type: MariaDB
Server version: 10.4.27-MariaDB
Protocol version: 10
Server charset: UTF-8 Unicode (utf8mb4)
Database client version: libmysql - mysqlnd 8.2.0

Running through XAMPP with Apache/2.4.64 (Win64).

This is being run in the PHPMyAdmin SQL console, at the database level.

Solution

You're encountering the issue due to the fact that in MySQL, you can only reference columns that have either a PRIMARY KEY or a UNIQUE constraint. One way to fix this, is you can create a composite UNIQUE constraint on columns A and B in table T.

ALTER TABLE T
ADD UNIQUE (A, B);

This should create the table U without any errors, and the foreign keys will reference the composite key (A, B) in table T.

DBFIDDLE