1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
|
/*********************************************
per_Personne
**********************************************/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[per_Personne]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[per_Personne]
(
per_ID INT IDENTITY(1,1) NOT NULL,
per_Nom VARCHAR(256) ,
per_Prenom VARCHAR(128) ,
CONSTRAINT pk_per_ID PRIMARY KEY CLUSTERED (per_ID),
)
END
GO
/*********************************************
enf_Enfant
**********************************************/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[enf_Enfant]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[enf_Enfant]
(
enf_ID INT IDENTITY(1,1) NOT NULL,
per_ID_Parent_fk INT NOT NULL,
sex_ID_Sexe_fk VARCHAR(16) NOT NULL,
enf_DateNaissance DATETIME,
CONSTRAINT pk_enf_ID PRIMARY KEY CLUSTERED (enf_ID),
CONSTRAINT fk_enf_per_ID FOREIGN KEY (per_ID_Parent_fk) REFERENCES per_Personne(per_ID),
)
CREATE INDEX [I_per_ID_Parent_FK] ON [dbo].[enf_Enfant] (per_ID_Parent_fk)
END
GO
INSERT INTO [dbo].[per_Personne] ([per_Nom], [per_Prenom]) VALUES ('COTILLARD' , 'Jean' );
INSERT INTO [dbo].[per_Personne] ([per_Nom], [per_Prenom]) VALUES ('DUJARDIN' , 'Richard' );
INSERT INTO [dbo].[per_Personne] ([per_Nom], [per_Prenom]) VALUES ('ANCONINA' , 'Marion' );
GO
INSERT INTO [dbo].[enf_Enfant] ([per_ID_Parent_fk], [sex_ID_Sexe_fk], [enf_DateNaissance]) ( SELECT [per_ID], 'Feminin', CONVERT(DATETIME, '01/01/2007', 103) FROM [dbo].[per_Personne] WHERE ( [dbo].[per_Personne].[per_Nom] = 'COTILLARD' AND [dbo].[per_Personne].[per_Prenom] = 'Jean' ))
INSERT INTO [dbo].[enf_Enfant] ([per_ID_Parent_fk], [sex_ID_Sexe_fk], [enf_DateNaissance]) ( SELECT [per_ID], 'Masculin', CONVERT(DATETIME, '02/01/2007', 103) FROM [dbo].[per_Personne] WHERE ( [dbo].[per_Personne].[per_Nom] = 'COTILLARD' AND [dbo].[per_Personne].[per_Prenom] = 'Jean' ))
INSERT INTO [dbo].[enf_Enfant] ([per_ID_Parent_fk], [sex_ID_Sexe_fk], [enf_DateNaissance]) ( SELECT [per_ID], 'Masculin', CONVERT(DATETIME, '01/02/2010', 103) FROM [dbo].[per_Personne] WHERE ( [dbo].[per_Personne].[per_Nom] = 'DUJARDIN' AND [dbo].[per_Personne].[per_Prenom] = 'Richard' ))
INSERT INTO [dbo].[enf_Enfant] ([per_ID_Parent_fk], [sex_ID_Sexe_fk], [enf_DateNaissance]) ( SELECT [per_ID], 'Masculin', CONVERT(DATETIME, '02/02/2010', 103) FROM [dbo].[per_Personne] WHERE ( [dbo].[per_Personne].[per_Nom] = 'DUJARDIN' AND [dbo].[per_Personne].[per_Prenom] = 'Richard' ))
INSERT INTO [dbo].[enf_Enfant] ([per_ID_Parent_fk], [sex_ID_Sexe_fk], [enf_DateNaissance]) ( SELECT [per_ID], 'Feminin', CONVERT(DATETIME, '03/02/2010', 103) FROM [dbo].[per_Personne] WHERE ( [dbo].[per_Personne].[per_Nom] = 'DUJARDIN' AND [dbo].[per_Personne].[per_Prenom] = 'Richard' ))
INSERT INTO [dbo].[enf_Enfant] ([per_ID_Parent_fk], [sex_ID_Sexe_fk], [enf_DateNaissance]) ( SELECT [per_ID], 'Feminin', CONVERT(DATETIME, '01/03/2014', 103) FROM [dbo].[per_Personne] WHERE ( [dbo].[per_Personne].[per_Nom] = 'ANCONINA' AND [dbo].[per_Personne].[per_Prenom] = 'Marion' ))
GO |
Partager