-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, 2012 and Azure
-- --------------------------------------------------
-- Date Created: 02/28/2018 15:05:10
-- 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_AxeThematique]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Thematiques] DROP CONSTRAINT [FK_AxeThematique];
GO
IF OBJECT_ID(N'[dbo].[FK_ThematiqueAction]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Actions] DROP CONSTRAINT [FK_ThematiqueAction];
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].[Axes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Axes];
GO
IF OBJECT_ID(N'[dbo].[Thematiques]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Thematiques];
GO
IF OBJECT_ID(N'[dbo].[Actions]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Actions];
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 'ActionAxes'
CREATE TABLE [dbo].[ActionAxes] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nom] nvarchar(max)  NOT NULL,
    [Neutralise] bit  NOT NULL,
    [Ordre] int  NOT NULL
);
GO

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

-- Creating table 'Actions'
CREATE TABLE [dbo].[Actions] (
    [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,
    [ActionThematiqueId] 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 'ActionAxes'
ALTER TABLE [dbo].[ActionAxes]
ADD CONSTRAINT [PK_ActionAxes]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

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

-- Creating primary key on [Id] in table 'Actions'
ALTER TABLE [dbo].[Actions]
ADD CONSTRAINT [PK_Actions]
    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 [ActionAxeId] in table 'ActionThematiques'
ALTER TABLE [dbo].[ActionThematiques]
ADD CONSTRAINT [FK_ActionAxeActionThematique]
    FOREIGN KEY ([ActionAxeId])
    REFERENCES [dbo].[ActionAxes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_ActionAxeActionThematique'
CREATE INDEX [IX_FK_ActionAxeActionThematique]
ON [dbo].[ActionThematiques]
    ([ActionAxeId]);
GO

-- Creating foreign key on [ActionThematiqueId] in table 'Actions'
ALTER TABLE [dbo].[Actions]
ADD CONSTRAINT [FK_ActionThematiqueAction]
    FOREIGN KEY ([ActionThematiqueId])
    REFERENCES [dbo].[ActionThematiques]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_ActionThematiqueAction'
CREATE INDEX [IX_FK_ActionThematiqueAction]
ON [dbo].[Actions]
    ([ActionThematiqueId]);
GO

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