You can accept the eventual inconsistency in a physical modeling [0,N]
as in the Reply from @Victorstafusa. Or use a database that accepts inconsistencies in the middle of transactions. This option is not common, I particularly only know the Sqlite that does this.
How to proceed in this case? Well, in Sqlite they call this foreign key consistency "deferred foreign key". It’s in section 4.2 of that Article explaining about foreign keys.
The concept of the "deferred key" is:
If I’m in a transaction, then occasionally consistency will be achieved, I don’t need to check every instant that everything is consistent
Usually the concept used with "foreign key" is what Sqlite calls "instant foreign key":
No matter where I am or where I’m going, everything needs to be consistent at any time!!!
In this "foreign key deferred" consistency model, just start a transaction before the inserts and then commit when you finish operating.
I find this consistency modeling particularly valuable only after transaction termination when you have circular data.
Imagine that you are modelling an echidna. These strange animals, not only are they one of the few oviparous mammals, they have a multiple-headed penises. And the males use these heads in a very interesting way: they take turns which head is going to be used to copulate, so that all the heads are used before repeating one of them.
ERRATA: the equidna does not take turns from the head of the penis as I had mentioned in the paragraph above. In fact, it expels through the seminal ducts up to two waves of semen by separate heads, but it is worth the mental exercise
For those who don’t know these strange beings, with the body of a hedgehog and the tongue of an anteater that lays eggs and feeds his descendants, follow his photo (courtesy dug by @Bacco): link to the equidna
In this case, we could model like this:
create table equidna (
id int primary key,
tag varchar(50) not null
)
create table penis_equidna (
id int primary key,
equidna_id int not null,
prox_penis_id int not null,
em_uso int not null default 0,
FOREIGN KEY (equidna_id) REFERENCES equidna(id),
FOREIGN KEY (prox_penis_id) REFERENCES penis_equidna(id) NOT DEFERRABLE
)
Note that I am here creating a circular list of echidna penises. To update what the next penis will be in use for the next copulation of equidna 13, just run the following update:
WITH p_em_uso AS (
SELECT
id,
prox_penis_id,
anterior_penis_id,
equidna_id
FROM
penis_equidna
WHERE
em_uso = 1
)
UPDATE penis_equidna
SET
em_uso = CASE
WHEN EXISTS (SELECT 1 FROM p_em_uso WHERE p_em_uso.prox_penis_id = penis_equidna.id AND p_em_uso.equidna_id = penis_equidna.equidna_id) THEN 1
ELSE 0
END
WHERE
equidna_id = 13
Not checked query yet, I hope to check soon and correct any and possible error contained in it
In this case, to register the equidna 13 and its 4 penises, it would have been necessary:
INSERT INTO equidna(id, tag) VALUES (13, 'abc')(
BEGIN; -- começa uma transação SQLite
INSERT INTO penis_equidna(id, equidna_id, prox_penis_id, em_uso) VALUES (1, 13, 2, 1);
INSERT INTO penis_equidna(id, equidna_id, prox_penis_id, em_uso) VALUES (2, 13, 4, 0), (4, 13, 3, 0), (3, 13, 1, 0)
COMMIT;
Note that consistency will only be achieved when the last line is inserted, thus closing the circle.
Having said this consistency model, I can’t model in any obvious way without using check constraints
an abusive relationship of [1,N]
for [1,N]
. I think it would be better to allow this inconsistency from scratch. It doesn’t seem right after removing a house I be required to remove a person (using the @Victorstafusa response template).
A real case of this modeling
I was chatting when @pussycat arose with a problem of structure modeling in the database (beginning more or less here).
His problem I identified more or less as the following:
I have fire sensors in several places. For example, km 12 (in the state of SP) of the BR-116 is the location identified by 1
, The km 13 of it is the place identified by 2
.
These sensors trigger alerts, which should be stored in the bank. These alerts come with the sensor identifier that triggered it and the intensity of the fire identified. However, in theory, a single fire point can trigger alarms on several sensors at the same location. So, each location can only have one fire point "on" at a time, and every warning always refers to a fire point.
I was able to infer the following modeling in the MER model:
In this case, I also detected an important detail: the insertion of a alerta
can generate a new ponto_queimada
! As if it were a creation trigger!
So, how do you do that? Well, I thought I’d actually use a trigger. It could have been a procedure in SQL or some programmatic solution at the application level. Trigger
it seemed more natural to me, however.
The idea of mine trigger
is:
- check whether a burn point already exists in that region
that burning point can’t have been contained
- if there is no such burn point, insert it
- now the burn point simply exists (if it did not exist before, now it exists), so I can enter the alert pointing to the correct alert
I thought about that trigger
replacing an insert. In case, I thought to have the alertas_view_insercao
, in which she would not have the information of id_ponto_queimada
/foreign key to burn point. Follow a pseudocode for this trigger
(original):
create trigger insteadof insert on alertas_view_insercao as
begin trigger
insert into ponto_queimada
select
'N'as CONTIDA
... informações
where not exists (
select top 1 1
from ponto_queimada pq
inner join alerta a on pq.id_ponto_queimada = a.id_ponto_queimada
inner join sensor s on a.id_sensor = s.id_sensor
inner join local l on l.id_local = s.id_local
where pq.contida = 'N' and l.id_local = inserted.id_local
);
with q as ( --se não existia um ponto_queimada antes, agora existe...
select top 1 pq.id_ponto_queimada
from ponto_queimada pq
inner join alerta a on pq.id_ponto_queimada = a.id_ponto_queimada
inner join sensor s on a.id_sensor = s.id_sensor
inner join local l on l.id_local = s.id_local
where pq.contida ='N' and l.id_local = inserted.id_local
)
inser into alerta
select
q.id_ponto_queimada,
... informações
from q
end trigger
The idea of trigger
in view
is:
- how is a
view
, the "user" can only inform the columns that ME want
- unused
insteadof
, the effect of the insertion is replaced by the content of the trigger
Several banks support operations in views
(Sqlite, SQL Server, etc). The moment insteadof
is known as instead of
(with space) by Sqlite.
With that trigger
, when registering a new alerta
through the alertas_view_insercao
, the bank itself will be concerned to inform which ponto_queimada
this alert refers. The bank itself checks whether there is a ponto_queimada
and, in its non-existence, will create a new.
I think you should have looked for a less controversial example than talking about equidna penises.
– Victor Stafusa
It seems to me that the example of equidnas is a good to demonstrate recursive or reflective relationships, the example of the question does not refer to this situation.
– Jonathas B. C.
@Jonathasb.Cavalcante and the use of the deferred key? Saw how she avoids the deadlock?
– Jefferson Quesado
Yes @Jeffersonquesado , although I had already worked with sqlite I had not given due attention to the key deferida, It was very good the explanation.
– Jonathas B. C.
@Jonathasb.C., I added a recent case that I took from modeling
1..[1,n]
. The solution was through a trigger to keep the bank consistent– Jefferson Quesado
Got mass example, vlw @Jeffersonquesado
– Jonathas B. C.