fichecollege/CD67.FicheCollege.BDD/dbo/Tables/Colleges.sql

CREATE TABLE [dbo].[Colleges] (
    [Id]               INT            IDENTITY (1, 1) 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,
    [PrincipalSid]     NVARCHAR (64)  NULL,
    [AdjointSid]       NVARCHAR (64)  NULL,
    [Gestionnaire1Sid] NVARCHAR (64)  NULL,
    [Gestionnaire2Sid] NVARCHAR (64)  NULL,
    CONSTRAINT [PK_Colleges] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_AnneeCollege] FOREIGN KEY ([Annee_Id]) REFERENCES [dbo].[Annees] ([Id]),
    CONSTRAINT [FK_Colleges_Territoires] FOREIGN KEY ([Territoire_Id]) REFERENCES [dbo].[Territoires] ([Id]),
    CONSTRAINT [FK_Colleges_TypesCollege] FOREIGN KEY ([TypeCollege_Id]) REFERENCES [dbo].[TypesCollege] ([Id])
);


GO
CREATE NONCLUSTERED INDEX [IX_FK_Colleges_Territoires]
    ON [dbo].[Colleges]([Territoire_Id] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_FK_Colleges_TypesCollege]
    ON [dbo].[Colleges]([TypeCollege_Id] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_FK_AnneeCollege]
    ON [dbo].[Colleges]([Annee_Id] ASC);