create database if not exists temp ;
use temp ;
drop table if exists INDIVIDU_GENE ;
create database if not exists claystone ;
use claystone ;
drop table if exists individual ;
CREATE TABLE individual(
id_geneal INT,
matricule varchar(8) not null,
CONSTRAINT INDIVIDUAL_PK PRIMARY KEY(id_geneal),
CONSTRAINT INDIVIDUAL_AK UNIQUE(matricule)
);
insert into individual (id_geneal, matricule)
values
(1, 'mat 01')
, (2, 'mat 02')
, (3, 'mat 03')
;
select * from individual ;
use temp ;
drop table if exists INDIVIDU_EXT ;
drop table if exists INDIVIDU_GENE ;
drop table if exists INDIVIDU ;
CREATE TABLE INDIVIDU(
id_Individu INT,
truc_individu varchar(20),
CONSTRAINT INDIVIDU_PK PRIMARY KEY(id_Individu)
);
insert into INDIVIDU (id_Individu, truc_individu)
values
(1, 'truc 1')
, (2, 'truc 2')
, (3, 'truc 3')
;
select * from INDIVIDU ;
CREATE TABLE INDIVIDU_GENE(
id_Individu_gene INT,
id_geneal INT NOT NULL,
PRIMARY KEY(id_individu_gene),
UNIQUE(id_geneal),
FOREIGN KEY(id_individu_gene) REFERENCES INDIVIDU(id_Individu),
FOREIGN KEY(id_geneal) REFERENCES claystone.individual(id_geneal)
);
insert into INDIVIDU_GENE (id_individu_gene, id_geneal)
values
(1, 1)
, (2, 2)
, (3, 3)
;
select * from INDIVIDU_GENE ;
CREATE TABLE INDIVIDU_EXT(
id_Individu INT,
nom VARCHAR(50) NOT NULL,
prenom VARCHAR(50) NOT NULL,
rqExt VARCHAR(50) NOT NULL,
PRIMARY KEY(id_Individu),
UNIQUE(nom, prenom, rqExt),
FOREIGN KEY(id_Individu) REFERENCES INDIVIDU(id_Individu)
);
insert into INDIVIDU_EXT (id_Individu, nom, prenom, rqExt)
values
(1, 'volfoni', 'raoul', 'rq1')
, (2, 'volfoni', 'paul', 'rq2')
, (3, 'volfoni', 'paul', 'rq2bis')
;
select * from INDIVIDU_EXT ;
Partager