You can accept the eventual inconsistency in a physical modeling [0,N] as in the @VictorStafusa answer. 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 as "deferral foreign key". It is in Section 4.2 of this article explaining about keys foreign.

The concept of the "Deferred key" is:

If I'm inside a transaction, then occasionally consistency will be achieved, I don't need to check every second if everything is consistent

Normally the concept used with "foreign key" is what SQLite calls "immediate foreign key":

No matter where I am or where I am going, everything needs to be consistent at any rate instant!!!

In this "deferred foreign key" consistency model, simply initiate a transaction before inserts and then commit when you finish trading.

I find particularly valuable this consistency modeling only after the transaction ends when you have circular data.

Imagine that you are modeling an echidna. These strange animals, not being enough to be one of the few oviparous mammals, have a penis of multiple heads . 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 : echidna does not take turns of the head of the penis as I had mentioned in the paragraph above. In fact, it expels through the seminal ducts up to two flights of semen by distinct heads, but it is worth the mental exercise

For those who do not know these strange beings, with hedgehog body and anteater tongue that boots eggs and gives suckling to their offspring, follow their photo (courtesy dug by @Bacco): link to the echidna

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 penises of echidnas. To update which will be the next penis to be in use for the next copulation of echidna 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

Query no checked yet, I hope to check soon and correct any and all errors contained therein

In the case, to register echidna 13 and its 4 penises, it would have been necessary this:

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 row is inserted, thus closing the circle.

Having said this consistency model, I can't model in any obvious way without using check constraints in an abusive way a relationship from [1,N] to [1,N]. Up I think it would be better to allow this inconsistency from scratch. It doesn't seem right to me after removing a house I would be required to remove a person (using @VictorStafusa's answer modeling).

A real case of this modeling

Was silly in the chat when @cat came up with a structure modeling problem in the database (start more or less here).

His problem I identified more or less as the next:

I have fire sensors in several locations. For example, km 12 (in the state of SP) of BR-116 is the location identified by 1, while km 13 of it is the location identified by 2.

These sensors trigger alerts, which should be stored in the bank. These alerts come with the identifier of the sensor that triggered it and the intensity of the identified Fire. However, in theory, the same fire point can trigger alarms on several sensors in the same place. So, each location can only have one "on" fire point at a time, and every alert always refers to one fire point.

I was able to infer the following modeling in the MER model:

In the case, I also detected an important detail: inserting a alerta can generate a new ponto_queimada! As if it were a trigger of creation!

So how to do this? 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 seemed more natural to me, though.

My idea trigger is:

1. check if there is already a burn point in that region
   this burn point cannot have been contained
2. if this burn point does not exist, insert it
3. now the burn point simply exists (if it didn't exist before, it now exists), so I can insert the alert pointing to the correct alert

I thought about this trigger replacing an insert. In case, I thought I would have the alertas_view_insercao, in which it would not have the information of id_ponto_queimada/foreign key to burn point. It follows 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:

1. since it is a view, the "user" can only inform the columns that I want
2. by using insteadof, the insertion effect is replaced by content of trigger

Several banks support operations in views (SQLite , SQL Server, etc). The moment insteadof is known as instead of (with space) by SQLite.

With this trigger, when registering a new alerta through alertas_view_insercao, the bank itself will worry about informing which ponto_queimada this alert refers to. The bank itself checks whether there is a suitable ponto_queimada and, in its non-existence, will create a new one.