C
Beyond help describe the relationship in the models, foreign keys are mainly used to maintain data integrity, i.e. imagine that you have two tables linked by a foreign key and have data in the table B linked to a specific line in the table A, then you, if you try to delete this line specifies the bank will prevent you and will send you an error.Before the foreign key we had to control and do this kind of prevention in the application layer or with "triggers", it was a very difficult job and was not always efficient.I'll quote some types of relationships:one-to-many, 1 for many (1:nIf in the table A I have a line with id 3 and the table B I have two lines linked to id 3 and I try to delete this line from the table A an error will occur.1 for N, one-to-many or 1:N to refer to one of the types of relationships that can be established between the two-table fields: each tuple of a table can relate to N tuples of the other table. However, each tuple of the second table only relates to a single tuple of the first. https://pt.wikipedia.org/wiki/1_para_N many-to-many, many for many (n:m)If in the table A we have two ids 35 (joon) and 36 (maria) and in the table B we have the following values:id | nome
1 | carro
2 | casa
3 | terreno
4 | kitnet
Then we will have a third table that will make the interaction between A and B, we can call it A_tem_B that in an example would be:id_A | id_B
1 | 35
1 | 36
2 | 35
3 | 36
4 | 36
What basically the table A_tem_B is saying,João (id 35) has a carMaria (id 36) has a carJohn has homeMary has landMaria has a kitnetIf I try to delete kitnet (id 4), an error will occur, because it is linked to Mary.If I try to delete carro (id 1), an error will occur, because Mary and John are linked to the car.In the relational model of databases, any expression is used N to N, many-to-many (many-to-many), or N:N (sometimes also expressed as N:M), to refer to one of the types of relationships that can be established between the two-table fields: for each field value of a table, there may be N values in the field of the other table and vice versa.In relational databases, this relationship is consumed through a link table that matches both sides with relationship 1 to N for each side. The primary key comes naturally, and is composed of the primary keys of the interconnected tables. The example is in mysql and innoDB and is an example of many for many, i.e. two users can have the same hobby (I don't know which bank is using), sql:CREATE TABLE IF NOT EXISTS usuarios (
idusuarios INT NOT NULL AUTO_INCREMENT,
nome VARCHAR(45) NULL,
PRIMARY KEY (idusuarios) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS hobbies (
idhobbies INT NOT NULL AUTO_INCREMENT,
hobbie VARCHAR(45) NULL,
PRIMARY KEY (idhobbies) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS usuarios_has_hobbies (
usuarios_idusuarios INT NOT NULL,
hobbies_idhobbies INT NOT NULL,
PRIMARY KEY (usuarios_idusuarios, hobbies_idhobbies) ,
INDEX fk_usuarios_has_hobbies_hobbies1_idx (hobbies_idhobbies ASC) ,
INDEX fk_usuarios_has_hobbies_usuarios_idx (usuarios_idusuarios ASC) ,
CONSTRAINT fk_usuarios_has_hobbies_usuarios
FOREIGN KEY (usuarios_idusuarios)
REFERENCES usuarios (idusuarios)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_usuarios_has_hobbies_hobbies1
FOREIGN KEY (hobbies_idhobbies)
REFERENCES hobbies (idhobbies)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Then enter a user and a hobby, then take the id from hooby and try to add with a user id that does not exist, will give this error:1452 - Cannot add or update a child row: a foreign key constraint fails (foo.usuarios_has_hobbies, CONSTRAINT fk_usuarios_has_hobbies_hobbies1 ♪hobbies_idhobbies) REFERENCES hobbies (idhobbies) ON DELETE NO ACTION ON UPDATE NO ACTION)However by its explanation if the example of hobbies is hypothetical, so perhaps your need is not n:m, it may be 1:n, thus the example would be like this: CREATE TABLE IF NOT EXISTS usuarios (
idusuarios INT NOT NULL AUTO_INCREMENT,
nome VARCHAR(45) NULL,
PRIMARY KEY (idusuarios) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS hobbies (
idhobbies INT NOT NULL AUTO_INCREMENT,
hobbie VARCHAR(45) NULL,
usuarios_idusuarios INT NOT NULL,
PRIMARY KEY (idhobbies) ,
INDEX fk_hobbies_usuarios_idx (usuarios_idusuarios ASC) ,
CONSTRAINT fk_hobbies_usuarios
FOREIGN KEY (usuarios_idusuarios)
REFERENCES usuarios (idusuarios)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
In this case I created a user:INSERT INTO usuarios (idusuarios, nome) VALUES ('João'); #id 1
So try to create a hobby for him, like this:INSERT INTO hobbies (hobbie , usuarios_idusuarios ) VALUES ('basquete', 1); #id: 1
Then try to create a hooby for a user that doesn't exist:INSERT INTO hobbies (hobbie , usuarios_idusuarios ) VALUES ('natação', 1900);
This error should occur because the id 1900 does not exist in the table usuarios:1452 - Cannot add or update a child row: a foreign key constraint fails (foo2.hobbies, CONSTRAINT fk_hobbies_usuarios ♪usuarios_idusuarios) REFERENCES usuarios (idusuarios) ON DELETE NO ACTION ON UPDATE NO ACTION) I did the tests here (locally) and it worked, occurring the errors cited when due.Software to model in MysqlA good software to get around these problems when modeling is the MySQL Workbench (if you use mysql) that supports Windows, Linux and OS X: http://dev.mysql.com/downloads/workbench/ O workbench it is very simple to use, but it is not easy to explain with words (perhaps in the future I edit this answer), anyway follows 3 videos lessons that can be useful: https://www.youtube.com/watch?v=QGsgiZLbgU4 Relationship between two tables: https://www.youtube.com/watch?v=7HryLal--ZI DER Modeling: https://www.youtube.com/watch?v=4RMtUtxbEz4