There are two tables between which you need to create a many-to-many relationship:

CREATE TABLE my_contacts ( contact_id INT NOT NULL AUTO_INCREMENT, last_name VARCHAR(30), first_name VARCHAR(20), email VARCHAR(50), gender CHAR(1), birthday DATE, PRIMARY KEY (contact_id) ); CREATE TABLE interests ( int_id INT NOT NULL AUTO_INCREMENT, interest VARCHAR(20), PRIMARY KEY (int_id) );

For many-to-many communications, you need to create an additional table:

 CREATE TABLE contact_interest ( contact_id INT NOT NULL, int_id INT NOT NULL );

How to create all the links in the additional table? You need to make the two columns that make up the additional table be a composite primary key, as well as foreign keys.

    1 answer 1

    Make both tables with ENGINE=InnoDB because as soon as this engine supports foreign keys.

     CREATE TABLE contact_interest ( contact_id INT NOT NULL, int_id INT NOT NULL, primary key (contact_id, int_id), FOREIGN KEY (contact_id) REFERENCES my_contacts(contact_id), FOREIGN KEY (int_id) REFERENCES interests(int_id) ) ENGINE=InnoDB;

    In FOREIGN KEY you can add ON DELETE CASCADE if you want to automatically delete records from this table in case of deleting the record from the parent table.

    • Why in the description of the table does not show that the column is both a primary key and external? - jisecayeyo 4:16 pm
    • @jisecayeyo because syntax is not required. All foreign keys and a composite primary key are described by separate sentences. And you can see which columns are included in the keys, and some means of drawing the base on the basis of this information will mark next to the column in the figure that it is included in some keys - Mike
    • Another such question. In the job of restricting an external key, the word CONSTRAINT can be used. What is it for? - jisecayeyo 4:27 pm
    • one
      @jisecayeyo In MySQL, it is necessary to set the name of the constraint constraint key_NAME foreign key ... If it is not present, then some default name is assigned. Knowing exactly the name, you can then write scripts to remove the restriction: alter table table_name drop foreign key key_NAME . And it is needed for compatibility, in other SQL dialects (for example, in oracle) the constraint keyword is mandatory - Mike