Doubt MySQL Foreign Key

Question

Doubt MySQL Foreign Key

Navigation

#1 by (0 votes)

1

First of all, I'm not an expert, much less with SQL (I'm just taking my first steps on how it should be used correctly). Of course, you can understand if I make serious mistakes. The doubt is the next one: I have 3 tables: networks, people, vendors.

Networks

campo       tipo        null    key   default   extra 
idred       int(11)     NO      PRI   NULL      auto_increment
fb          text        YES           NULL  
in          text        YES           NULL  
tw          text        YES           NULL

People

campo           tipo        null    key    default  extra
idpersona       int(11)     NO      PRI    NULL     auto_increment
nombre          text        NO             NULL
apellido        text        NO             NULL
dni             int(11)     NO      UNI    NULL
direccion       text        NO             NULL
telefono        text        NO             NULL
email           text        YES            NULL
imagen          text        NO             NULL
fecha_alta      text        NO             NULL
num_redes       int(11)     YES     MUL    NULL

Vendors

campo           tipo        null    key     default     extra
idvendedor      int(11)     NO      PRI     NULL        auto_increment
usuario_nombre  varchar(45) YES             NULL
usuario_contra  varchar(45) YES             NULL
condicion       int(11)     NO              NULL
num_persona     int(11)     YES     MUL     NULL

The foreign keys themselves are:

sellers.num_people - > corresponds to personas.idpersona
personas.num_redes - > corresponds to networks.idredes

Both with the same restrictions (ON UPDATE CASCADE ON DELETE CASCADE). The problem arises when I try to remove directly from the sellers table:

DELETE FROM 'vendedores' WHERE vendedores.idvendedor='1';

As I understood it, the cascading restriction for the deletion would remove the records in the other tables that 'share' that data. Any way to solve this or should I delete record by record? Of course, thank you very much:).

Note : If more data is needed, I'm aware.

mysql sql

asked by Faju 21.09.2017 в 19:46

source

1 answer

Routes with angle [closed] As an NgModule implement in a custom field

score 0 · Accepted Answer

You must be clear about the types of Foreign Key that exist in MySQL

On Delete Restrict

The database will return an error. This prevents the child records from being orphaned and keep their relationship.

On Delete Cascade

Deleting a record will erase all of your children, such as a Waterfall

On Delete Set Null

When you delete a record, the child records will be updated in NULL . Keep in mind that the child records allow values NULL otherwise it will throw a error

On Delete Set Default

This restriction works similarly to the "on delete set null" type, except that, instead of setting the FK column of the secondary table to null, it sets it to the default preset value of the column definition. This default value must exist in the primary table.

Considering how the Foreign Keys work, I think you can update your database to how you want it to work when you delete it.

I recommend you not delete any records unless they are really "garbage records" because you create your database to store information in time, what they recommend is to use a field called inactive, if this field is true You can not see it in your program