Create table of unique foreign keys

Question

I am using MySQL and I have two tables of the form :

mysql> describe ing_categories;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| ID_Category | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| category    | varchar(64)      | NO   | UNI | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> describe ing_titles;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| ID_Title | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title    | varchar(128)     | NO   | UNI | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)  

I have a third table containing foreign key records, the foreign keys are to the above tables:

mysql> describe ing_title_categories;
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| ID_Title_Category | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| ID_Title          | int(10) unsigned | NO   | MUL | NULL    |                |
| ID_Category       | int(10) unsigned | NO   | MUL | NULL    |                |
+-------------------+------------------+------+-----+---------+----------------+  

How do I create the ing_titles_categories table so that the pair of foreign keys is unique?

Here is my SQL statement:

CREATE TABLE ing_title_categories
(
    ID_Title_Category INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ID_Title INTEGER UNSIGNED NOT NULL,
    ID_Category INTEGER UNSIGNED NOT NULL,
    FOREIGN KEY fk_title(ID_Title)
        REFERENCES ing_titles(ID_Title)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    FOREIGN KEY fk_category(ID_Category)
        REFERENCES ing_categories(ID_Category)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB;

I've searched other questions and they involve making an index for the foreign keys; but I don't want to index by the foreign keys, I want the constraint to be unique for the pair of foreign keys.

Tools:
MySQL Server version: 5.6.26

Notes:

1. I am not using PHP, but C++ via MySQL C++ Connector, so please no PHP examples.

Answer 1

You add a unique or primary key constraint. Here is an example:

CREATE TABLE ing_title_categories
(
    ID_Title_Category INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ID_Title INTEGER UNSIGNED NOT NULL,
    ID_Category INTEGER UNSIGNED NOT NULL,
    FOREIGN KEY fk_title(ID_Title)
        REFERENCES ing_titles(ID_Title)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    FOREIGN KEY fk_category(ID_Category)
        REFERENCES ing_categories(ID_Category)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    UNIQUE (ID_Title, ID_Category)
) ENGINE=InnoDB;

The implementation of the unique constraint does create an index. However, that index is needed to enforce the constraint. It will also be used for queries, where appropriate.

Note that this is equivalent to:

create unique index idx_ing_title_categories_2 on ing_title_categories(ID_Title, ID_Category);

One advantage of putting the constraint in the CREATE TABLE statement is that you can use the CONSTRAINT keyword to give the constraint a meaningful name -- useful when you want to figure out what is happening when the constraint is violated.