Foreign Key (foreign key) referencing utility in the database

Question

I have a question in jobs with databases.

Exemplifying, as the image below:

Well, I know I should include in the table agendar_consulta the user who needs a consultation and the doctor who will consult him, and this is done through foreign keys referring to unique records of tables users and médicos.

Now the question:

What’s the use of me referencing Foreign Keys (foreign keys) of the table agendar_consulta the records of the tables users and médicos?

Wouldn’t have the same effect I leave the table agendar_consulta with the fields id_medico and id_user as simple fields (not Foreign Key) of Integer variable and only repeat id’s independently of other tables?

Answer 1

Rodrigo, foreign keys are useful to prevent actions that destroy the links between tables and also prevent invalid data from being inserted in a foreign key, because the value entered has to be an existing value in the table that is linked to this.

If you do not use foreign keys and only have one column, these rules will not be applied by default.

Using your example, if I had one id_user = 1 and then try to insert into the table agendar_consulta one user with id_user = 2 this insertion could not be done, would be scheduling a query for a user unregistered, such as scheduling a query with a id_medico non-existent, cannot perform a query if the id_medico does not exist in your database.

Answer 2

Integrity and security.

When using Foreign Key, we can ensure that no record will be physically added to the table agendar_consulta without a id_user and a id_medico valid.

Same goes for deletion: no record can be deleted from tables users and medicos if these are being used in the table agendar_consulta