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,
    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);