How can I correctly set 2 foreign keys constraint on this table implementing a many to many relationship between 2 tables?

Question

I am working on MariaDB (it is the same as working on MySQL).

I have some problem trying to set 2 foreign key constraints on 2 field of a table that implements a many-to-many relation between 2 tables.

So I have the following tables:

1. ACCOMODATION: each record represent an hotel and have the following fields:

    Field                                                            Type       Null Key Default    Extra                      
    ---------------------------------------------------------------------------------------------------------------------------
    id                                                               bigint(20) unsigned NO   PRI            auto_increment             
    user_id                                                          bigint(20) unsigned NO                                             
    accomodation_name                                                varchar(100) NO                                             
    description                                                      text       YES                                            
    nation                                                           varchar(100) YES                                            
    region                                                           varchar(100) YES                                            
    province                                                         varchar(100) YES                                            
    city                                                             varchar(100) YES                                            
    stars                                                            int(10) unsigned YES                                            
    geographical_position                                            point      YES                                            
    accomodation_typological_id                                      bigint(20) unsigned YES  MUL                                       
    accomodation_service_id                                          bigint(20) YES      
   

2. SERVICE: where each record represent a single service provided by an hotel of the previous table:

    Field                                                            Type       Null Key Default    Extra                      
    ---------------------------------------------------------------------------------------------------------------------------
    id                                                               bigint(20) NO   PRI            auto_increment             
    description                                                      varchar(255) NO       
   

Then I have created an ACCOMODATION_SERVICE table that implements the many-to-many relationship between the previous 2 tables, these are the fields:

Field                                                            Type       Null Key Default    Extra                      
---------------------------------------------------------------------------------------------------------------------------
id                                                               bigint(20) unsigned NO   PRI            auto_increment             
accomodation_id                                                  bigint(20) unsigned NO   MUL                                       
service_id                                                       bigint(20) unsigned NO                                             

So, into the accomodation_id field of this table I put an id of the accomodation table and into the service_id of this table I put an id of the service table.

How can I correctly specify that these 2 field have to be foreign keys for the accomodation and service tables? I think that I have to specify this referential constraint.

Answer 1

For your pivot table

CREATE TABLE accommodation_service(
id ............,
accommodation_id ............,
service_id ............,
FOREIGN KEY (accommodation_id)
    REFERENCES accommodation(id)
    ON DELETE CASCADE,
FOREIGN KEY (service_id)
        REFERENCES service(id)
        ON DELETE CASCADE,

)

I think this is the boilerplate you're looking for.

Answer 2

You need to do

ALTER TABLE accomodation_service ADD CONSTRAINT fk_accomodation_id FOREIGN KEY(accomodation_id) REFERENCES accomodate(id)

And similarly

ALTER TABLE accomodation_service ADD CONSTRAINT fk_service_id FOREIGN KEY(accomodationfk_service_id_id) REFERENCES service(id)