SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE UT_utilisateur(
   UT_ident INT IDENTITY,
   UT_nom VARCHAR(50) NOT NULL,
   PRIMARY KEY(UT_ident)
);

CREATE TABLE MO_monstre(
   MO_ident INT IDENTITY,
   MO_nom VARCHAR(50) NOT NULL,
   PRIMARY KEY(MO_ident)
);

CREATE TABLE FO_formation(
   FO_ident INT IDENTITY,
   FO_nom VARCHAR(50) NOT NULL,
   PRIMARY KEY(FO_ident)
);

CREATE TABLE FR_frenesie(
   MO_ident INT,
   FR_seq SMALLINT,
   FR_nom VARCHAR(50) NOT NULL,
   PRIMARY KEY(MO_ident, FR_seq),
   FOREIGN KEY(MO_ident) REFERENCES MO_monstre(MO_ident)
);
;

CREATE TABLE AQ_acquerir(
   MO_ident INT,
   FR_seq SMALLINT,
   UT_ident INT,
   PRIMARY KEY(MO_ident, FR_seq, UT_ident),
   FOREIGN KEY(MO_ident, FR_seq) REFERENCES FR_frenesie(MO_ident, FR_seq),
   FOREIGN KEY(UT_ident) REFERENCES UT_utilisateur(UT_ident)
);

CREATE TABLE RE_rencontrer(
   UT_ident INT,
   MO_ident INT,
   PRIMARY KEY(UT_ident, MO_ident),
   FOREIGN KEY(UT_ident) REFERENCES UT_utilisateur(UT_ident),
   FOREIGN KEY(MO_ident) REFERENCES MO_monstre(MO_ident)
);

CREATE TABLE AG_agreger(
   MO_ident INT,
   FO_ident INT,
   PRIMARY KEY(MO_ident, FO_ident),
   FOREIGN KEY(MO_ident) REFERENCES MO_monstre(MO_ident),
   FOREIGN KEY(FO_ident) REFERENCES FO_formation(FO_ident)
);

alter table AQ_acquerir
add constraint FK_AQ_AC
foreign key (UT_ident, MO_ident)
references RE_rencontrer (UT_ident, MO_ident)
;
insert into UT_utilisateur (UT_nom)
values ('dupont'), ('martin'), ('abadie')
;
insert into MO_monstre (MO_nom)
values ('raie des sables'), ('truite des sables'), ('scorpion')
;
insert into FO_formation (FO_nom)
values ('FO1'), ('FO2'), ('FO3')
;
insert into FR_frenesie (MO_ident, FR_seq, FR_nom)
values (1, 1, 'FR1'), (2, 1, 'FR2'), (3, 1, 'FR31'), (3, 2, 'FR32')
;
-- création des agrégations
insert into AG_agreger (MO_ident, FO_ident)
values (1, 1), (1, 2), (2, 1), (3, 1), (3, 3)
;
-- création des rencontres
insert into RE_rencontrer (UT_ident, MO_ident)
values (1, 1), (1, 2), (2, 2), (3, 3)
;
-- qui a rencontré quoi
select UTI.UT_nom as joueur
     , MON.MO_nom as monstre
from        UT_utilisateur as UTI
inner join  RE_rencontrer  as REN
   on REN.UT_ident = UTI.UT_ident
inner join  MO_monstre     as MON
   on MON.MO_ident = REN.MO_ident
-- acquisition de frénésies
insert into AQ_acquerir (MO_ident, FR_seq, UT_ident)
values (1, 1, 1), (2, 1, 1)
;
-- acquisition invalide : le joueur n'a pas rencontré le monstre qui possède la frénésie
insert into AQ_acquerir (MO_ident, FR_seq, UT_ident)
values (2, 1, 3)
;

joueur	monstre
dupont	raie des sables
dupont	truite des sables
martin	truite des sables
abadie	scorpion

Msg 547 Level 16 State 0 Line 93
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_AQ_AC". The conflict occurred in database "fiddle_e828546660ab484da435b49044fd19a9", table "dbo.RE_rencontrer".