-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, 2012 and Azure
-- --------------------------------------------------
-- Date Created: 02/28/2018 15:56:26
-- Generated from EDMX file: C:\dev\dotnet\FicheCollege\CD67.FicheCollege.Entity\EntityModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [FicheCollege];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Colleges_Territoires]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Colleges] DROP CONSTRAINT [FK_Colleges_Territoires];
GO
IF OBJECT_ID(N'[dbo].[FK_Colleges_TypesCollege]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Colleges] DROP CONSTRAINT [FK_Colleges_TypesCollege];
GO
IF OBJECT_ID(N'[dbo].[FK_FicheIdentite_College]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Identites] DROP CONSTRAINT [FK_FicheIdentite_College];
GO
IF OBJECT_ID(N'[dbo].[FK_AnneeCollege]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Colleges] DROP CONSTRAINT [FK_AnneeCollege];
GO
IF OBJECT_ID(N'[dbo].[FK_ActionEduAxeActionEduThematique]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ActionEduThematiques] DROP CONSTRAINT [FK_ActionEduAxeActionEduThematique];
GO
IF OBJECT_ID(N'[dbo].[FK_ActionEduThematiqueActionEdu]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ActionsEdu] DROP CONSTRAINT [FK_ActionEduThematiqueActionEdu];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[TypesCollege]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TypesCollege];
GO
IF OBJECT_ID(N'[dbo].[Territoires]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Territoires];
GO
IF OBJECT_ID(N'[dbo].[Colleges]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Colleges];
GO
IF OBJECT_ID(N'[dbo].[Identites]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Identites];
GO
IF OBJECT_ID(N'[dbo].[Annees]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Annees];
GO
IF OBJECT_ID(N'[dbo].[ActionEduAxes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ActionEduAxes];
GO
IF OBJECT_ID(N'[dbo].[ActionEduThematiques]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ActionEduThematiques];
GO
IF OBJECT_ID(N'[dbo].[ActionsEdu]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ActionsEdu];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'TypesCollege'
CREATE TABLE [dbo].[TypesCollege] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Libelle] nvarchar(255)  NOT NULL,
    [Ordre] int  NOT NULL
);
GO

-- Creating table 'Territoires'
CREATE TABLE [dbo].[Territoires] (
    [Id] nvarchar(10)  NOT NULL,
    [Libelle] nvarchar(255)  NOT NULL,
    [Referent_SID] nvarchar(64)  NULL,
    [Referent_Nom] nvarchar(255)  NULL,
    [Referent_Prenom] nvarchar(255)  NULL,
    [Referent_Structure] nvarchar(255)  NULL,
    [Referent_Login] nvarchar(255)  NULL,
    [Referent_Email] nvarchar(255)  NULL,
    [Ordre] int  NOT NULL
);
GO

-- Creating table 'Colleges'
CREATE TABLE [dbo].[Colleges] (
    [Id] int  NOT NULL,
    [Annee_Id] int  NOT NULL,
    [CodeRne] nvarchar(10)  NOT NULL,
    [Libelle] nvarchar(255)  NOT NULL,
    [Adresse] nvarchar(255)  NULL,
    [Code_Postal] nvarchar(5)  NULL,
    [TypeCollege_Id] int  NOT NULL,
    [Commune_Insee] nvarchar(5)  NULL,
    [Commune] nvarchar(255)  NOT NULL,
    [Canton] nvarchar(255)  NULL,
    [Territoire_Id] nvarchar(10)  NULL,
    [TAD] nvarchar(255)  NULL,
    [CDC] nvarchar(255)  NULL,
    [Tel] nvarchar(255)  NULL,
    [Fax] nvarchar(255)  NULL,
    [Email] nvarchar(255)  NULL
);
GO

-- Creating table 'Identites'
CREATE TABLE [dbo].[Identites] (
    [College_Id] int  NOT NULL,
    [Principal_SID] nvarchar(64)  NULL,
    [Principal_Login] nvarchar(255)  NULL,
    [Principal_Nom] nvarchar(255)  NULL,
    [Principal_Prenom] nvarchar(255)  NULL,
    [Principal_Email] nvarchar(255)  NULL,
    [Principal_Tel] nvarchar(255)  NULL,
    [Principal_Structure] nvarchar(255)  NULL,
    [Adjoint_SID] nvarchar(64)  NULL,
    [Adjoint_Login] nvarchar(255)  NULL,
    [Adjoint_Nom] nvarchar(255)  NULL,
    [Adjoint_Prenom] nvarchar(255)  NULL,
    [Adjoint_Email] nvarchar(255)  NULL,
    [Adjoint_Tel] nvarchar(255)  NULL,
    [Adjoint_Structure] nvarchar(255)  NULL,
    [Gestionnaire_SID] nvarchar(64)  NULL,
    [Gestionnaire_Login] nvarchar(255)  NULL,
    [Gestionnaire_Nom] nvarchar(255)  NULL,
    [Gestionnaire_Prenom] nvarchar(255)  NULL,
    [Gestionnaire_Email] nvarchar(255)  NULL,
    [Gestionnaire_Tel] nvarchar(255)  NULL,
    [Gestionnaire_Structure] nvarchar(255)  NULL,
    [Gestionnaire2_SID] nvarchar(64)  NULL,
    [Gestionnaire2_Login] nvarchar(255)  NULL,
    [Gestionnaire2_Nom] nvarchar(255)  NULL,
    [Gestionnaire2_Prenom] nvarchar(255)  NULL,
    [Gestionnaire2_Email] nvarchar(255)  NULL,
    [Gestionnaire2_Tel] nvarchar(255)  NULL,
    [Gestionnaire2_Structure] nvarchar(255)  NULL
);
GO

-- Creating table 'Annees'
CREATE TABLE [dbo].[Annees] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Libelle] nvarchar(12)  NOT NULL
);
GO

-- Creating table 'ActionEduAxes'
CREATE TABLE [dbo].[ActionEduAxes] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nom] nvarchar(max)  NOT NULL,
    [Neutralise] bit  NOT NULL,
    [Ordre] int  NOT NULL
);
GO

-- Creating table 'ActionEduThematiques'
CREATE TABLE [dbo].[ActionEduThematiques] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nom] nvarchar(max)  NOT NULL,
    [Neutralise] bit  NOT NULL,
    [Ordre] int  NOT NULL,
    [ActionEduAxeId] int  NOT NULL
);
GO

-- Creating table 'ActionsEdu'
CREATE TABLE [dbo].[ActionsEdu] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nom] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [Neutralise] bit  NOT NULL,
    [Ordre] int  NOT NULL,
    [ActionEduThematiqueId] int  NOT NULL,
    [Annee_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'TypesCollege'
ALTER TABLE [dbo].[TypesCollege]
ADD CONSTRAINT [PK_TypesCollege]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Territoires'
ALTER TABLE [dbo].[Territoires]
ADD CONSTRAINT [PK_Territoires]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Colleges'
ALTER TABLE [dbo].[Colleges]
ADD CONSTRAINT [PK_Colleges]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [College_Id] in table 'Identites'
ALTER TABLE [dbo].[Identites]
ADD CONSTRAINT [PK_Identites]
    PRIMARY KEY CLUSTERED ([College_Id] ASC);
GO

-- Creating primary key on [Id] in table 'Annees'
ALTER TABLE [dbo].[Annees]
ADD CONSTRAINT [PK_Annees]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ActionEduAxes'
ALTER TABLE [dbo].[ActionEduAxes]
ADD CONSTRAINT [PK_ActionEduAxes]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ActionEduThematiques'
ALTER TABLE [dbo].[ActionEduThematiques]
ADD CONSTRAINT [PK_ActionEduThematiques]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ActionsEdu'
ALTER TABLE [dbo].[ActionsEdu]
ADD CONSTRAINT [PK_ActionsEdu]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Territoire_Id] in table 'Colleges'
ALTER TABLE [dbo].[Colleges]
ADD CONSTRAINT [FK_Colleges_Territoires]
    FOREIGN KEY ([Territoire_Id])
    REFERENCES [dbo].[Territoires]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Colleges_Territoires'
CREATE INDEX [IX_FK_Colleges_Territoires]
ON [dbo].[Colleges]
    ([Territoire_Id]);
GO

-- Creating foreign key on [TypeCollege_Id] in table 'Colleges'
ALTER TABLE [dbo].[Colleges]
ADD CONSTRAINT [FK_Colleges_TypesCollege]
    FOREIGN KEY ([TypeCollege_Id])
    REFERENCES [dbo].[TypesCollege]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Colleges_TypesCollege'
CREATE INDEX [IX_FK_Colleges_TypesCollege]
ON [dbo].[Colleges]
    ([TypeCollege_Id]);
GO

-- Creating foreign key on [College_Id] in table 'Identites'
ALTER TABLE [dbo].[Identites]
ADD CONSTRAINT [FK_FicheIdentite_College]
    FOREIGN KEY ([College_Id])
    REFERENCES [dbo].[Colleges]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Annee_Id] in table 'Colleges'
ALTER TABLE [dbo].[Colleges]
ADD CONSTRAINT [FK_AnneeCollege]
    FOREIGN KEY ([Annee_Id])
    REFERENCES [dbo].[Annees]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_AnneeCollege'
CREATE INDEX [IX_FK_AnneeCollege]
ON [dbo].[Colleges]
    ([Annee_Id]);
GO

-- Creating foreign key on [ActionEduAxeId] in table 'ActionEduThematiques'
ALTER TABLE [dbo].[ActionEduThematiques]
ADD CONSTRAINT [FK_ActionEduAxeActionEduThematique]
    FOREIGN KEY ([ActionEduAxeId])
    REFERENCES [dbo].[ActionEduAxes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_ActionEduAxeActionEduThematique'
CREATE INDEX [IX_FK_ActionEduAxeActionEduThematique]
ON [dbo].[ActionEduThematiques]
    ([ActionEduAxeId]);
GO

-- Creating foreign key on [ActionEduThematiqueId] in table 'ActionsEdu'
ALTER TABLE [dbo].[ActionsEdu]
ADD CONSTRAINT [FK_ActionEduThematiqueActionEdu]
    FOREIGN KEY ([ActionEduThematiqueId])
    REFERENCES [dbo].[ActionEduThematiques]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_ActionEduThematiqueActionEdu'
CREATE INDEX [IX_FK_ActionEduThematiqueActionEdu]
ON [dbo].[ActionsEdu]
    ([ActionEduThematiqueId]);
GO

-- Creating foreign key on [Annee_Id] in table 'ActionsEdu'
ALTER TABLE [dbo].[ActionsEdu]
ADD CONSTRAINT [FK_ActionEduAnnee]
    FOREIGN KEY ([Annee_Id])
    REFERENCES [dbo].[Annees]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_ActionEduAnnee'
CREATE INDEX [IX_FK_ActionEduAnnee]
ON [dbo].[ActionsEdu]
    ([Annee_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------