nonexistent origin registration. How to bypass? Foreign Key

Asked

Viewed 59 times

2

I have a field in a table of celulas who asks for the number of celula that gave it origin.

Example: We have the table celulas:

idCelula,
nome,
etc ...
origem

That one origin ago reference at the idCelula of cell who created her (cell originating new cells).

It turns out that origem is foreign key referencing idCelula.

The problem: When we register the first cell, as there is not yet a origin, there will be a recording problem of hers in the Mysql.

Is there any clause case for example to get around this problem?

mysql sql

2 answers

2


If available, remove the restriction not null of the source field, so if a cell has no "source", it can be inserted into the database without problems.

  • yeah. that’s what I wouldn’t like. remove NOT NULL. But it will be the most viable!

  • Try to control the insertion through Stored Procedure, if it is the first insertion allows inserting without source, otherwise it does not allow.

  • good, but... how do you do it?

1

There are some options. All have advantages and disadvantages:

  1. you can remove the NOT NULL restriction from the source field. If you want to simulate NOT NULL in other records, forcing them to include an origin, you can use database resources to do so:

  2. you can keep NOT NULL and each "main" cell would self-reference; for example, the cell with idCelula = 1, would also have origem = 1. It is not very beautiful in terms of modeling, since for unknown values it is recommended to use NULL.

   idCelula  origem  nome
   1         1       Principal 1
   2         1         Segundo nível 1
   3         2           Terceiro nível 1
   4         4       Principal 2
   5         4         Segundo nível 2
   6         5           Terceiro nível 2

  • The problem here is that a cell never leaves itself because it doesn’t exist yet. Always need another cell as the source.

  • @Carlosrocha, is a matter of convention. There is a perfect modeling for all cases. You can agree that the cell with origem = NULL is an "original", you can agree that the cell with origem = idCelula is an "original", or you can find another rule to identify the "original" cells, for example include a field with the cell level in the hierarchy (0, 1, 2, 3, etc), and the cells with level 0 would be the "original", or you can include a cell with idCelula=0 and call it "original", etc etc etc. Each option has its advantages and disadvantages.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.