What is the use of using foreign keys?

Question

I’ve been learning about foreign keys and I’m having a hard time understanding what they’re useful for. Its purpose would be to "divide a table into several tables, leaving its data always atomic", but I could simply program a system safely to add a value in the column of a second table with the id of the first table, and have the same security when making queries, etc.

Let’s go to an example, where I have two tables users (id, username, email, password) and hobbies(id, hobbie, iduser). In this case the column iduser would be FK of id table users, what the real use of the foreign key would be?

In my understanding it would give security and integrity to the data, but it happened to me from the table users have 4 records, with the latest id being equal to 4 and even so I could insert a data into the table hobbies with the column iduser equal to 5, how can this happen when I don’t have a id user 5?

Another disappointment was knowing that the crud doesn’t change, I’ll just have the id of the first table in SESSION and use the crud normally by applying this value in the column iduser when inserting, and where when deleting, searching or changing.

I don’t understand the concept of FK, I don’t know how to use it or is it useless? Could you give me a light? I spent the day trying to understand about it, I understood, but in the application I see nothing more.

Answer 1

Besides help describe the relationship in the models, foreign keys are mainly used to maintain data integrity, ie imagine that you have two tables linked by a foreign key and have data in the table B linked to a specific row in the table A, then you, if you try to delete this line specifies the bank will prevent you and will send an error.

Before the foreign key we had to control and do this kind of prevention at the application layer or with "triggers", it was a very difficult job and was not always efficient.

I will quote some types of relationships:

one-to-Many, 1 for many (1:n):

If on the table A I have a row with id 3 and in the table B I have two lines attached to id 3 and I try to delete this row from table A an error will occur.

1 to N, one-to-many or 1:N (one-to-Many) to refer to one of the types of relationships that can be established between the fields of two tables: 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://en.wikipedia.org/wiki/One-to-many_(data_model)}

Many-to-Many, many to many (n:m)

If on the table A we have two ids 35 (john) and 36 (mary) and in the table B we have the following values:

id | nome
--------------
1  | carro
2  | casa
3  | terreno
4  | kitnet

Then we 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 is:

• John (id 35) has car
• Maria (id 36) has car
• João has a home
• Maria has land
• Maria has kitnet

If I try to delete kitnet (id 4), an error will occur because it is linked to Maria.

If I try to delete carro (id 1), an error will occur because Mary and John are linked to the car.

In the relational database model, any of the expressions N for N, many-to-many (Many-to-Many), or N:N (sometimes also expressed as N:M) is used to refer to one of the types of relationships that can be established between the fields of two tables: for each value of a table field, there may be N values in the other table field and vice versa.

In relational databases, this relationship is consummated through a link table that matches both sides with relationship 1 to N for each side. The primary key arises naturally, and is composed by the junction of the primary keys of the interconnected tables.

_{https://en.wikipedia.org/wiki/Many-to-many_(data_model)}

Even without foreign key we can have a relationship between different tables, but the hard thing is to maintain integrity even if using triggers for such service as I mentioned earlier.

Problem of the matter

If you are able to insert hobbies into the table is because there is something wrong in how you modeled, it is not a problem with FK but rather the way you did, see an example that works:

The example is in mysql and innoDB and is an example of many for many, ie two users can have the same hobby (do not know which database 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 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 FOREIGN KEY (hobbies_idhobbies) REFERENCES hobbies (idhobbies) ON DELETE NO ACTION ON UPDATE NO ACTION)

However by its explanation whether the example of hobbies is hypothetical, so maybe your need isn’t n:m, may be 1:n, so 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 creating a hooby for a user that doesn’t exist:

INSERT INTO `hobbies` (`hobbie` , `usuarios_idusuarios` ) VALUES ('natação',  1900);

This error must occur because 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 FOREIGN KEY (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 for modeling in Mysql

A good software to get around these problems when modeling is the Mysql Workbench (mysql) that supports Windows, Linux and OS X:

• http://dev.mysql.com/downloads/workbench/

The Workbench is quite simple to use, but it is not easy to explain with words (maybe 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
• MODELLING DER: https://www.youtube.com/watch?v=4RMtUtxbEz4

Answer 2

TL;DR

Foreign keys are all about maintaining data consistency.

Restrictions

Defining a foreign key creates a link between a source table record with a destination table record.

In general, if a field is a foreign key, each record contains in that field a valid identifier that references an existing record in the other table.

This means that the database restricts the values that can be placed in that field, checking whether the corresponding record actually exists.

It also restricts the removal of records from the referenced table, preventing you from removing records that are referenced by any foreign key.

Relaxation of restrictions

Some databases have mechanisms to disable restrictions, creating the risk of a change leaving records orphaned, that is, referencing other no longer existing records.

Another mechanism that some databases allow is to remove cascading records, that is, when deleting a record referenced by some foreign key, the record that holds the foreign key is also removed.

Use such features carefully, only if you need it badly.

Advantage of using foreign keys

The main advantage is to avoid inconsistencies. It is naive to think that the system will ensure that the records are in order for various reasons that no one can anticipate:

• Script with errors executed directly in the base
• Lack of transactional control in a multi-table operation
• Failure to manage reading or writing competitors
• Errors in system SQL
• Other systems using the same database
• That person who forgets to put WHERE in UPDATES and DELETES

The foreign key will prevent records with dependencies from being deleted unintentionally..

Side effects

Restrictions caused by defining a foreign key sometimes annoy developers, especially when they don’t understand the data model or when they still have little knowledge about the database.

For example, creating a script to change the frame of the referenced table can be a headache, especially if the primary key is modified or the field type changes.

It is also more difficult to create sample data, tests and functionality for data removal.

Anyway, it’s another case where safety creates a little more work.