-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, 2012 and Azure
-- --------------------------------------------------
-- Date Created: 02/01/2018 10:35:41
-- 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_Actions-CLAS_College]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Actions-CLAS] DROP CONSTRAINT [FK_Actions-CLAS_College];
GO
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

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

IF OBJECT_ID(N'[dbo].[Actions-CLAS]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Actions-CLAS];
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].[Territoires]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Territoires];
GO
IF OBJECT_ID(N'[dbo].[TypesCollege]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TypesCollege];
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] nvarchar(8)  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,
    [Annee_Id] int  NOT NULL
);
GO

-- Creating table 'Identites'
CREATE TABLE [dbo].[Identites] (
    [College_Id] nvarchar(8)  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 'ActionsCLAS'
CREATE TABLE [dbo].[ActionsCLAS] (
    [College_Id] nvarchar(8)  NOT NULL,
    [Action] bit  NULL,
    [Commentaire] nvarchar(max)  NULL
);
GO

-- Creating table 'Annees'
CREATE TABLE [dbo].[Annees] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [libelle] nvarchar(max)  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 [College_Id] in table 'ActionsCLAS'
ALTER TABLE [dbo].[ActionsCLAS]
ADD CONSTRAINT [PK_ActionsCLAS]
    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 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 [College_Id] in table 'ActionsCLAS'
ALTER TABLE [dbo].[ActionsCLAS]
ADD CONSTRAINT [FK_Actions_CLAS_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

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