SaidSoighiri94
72 lines
2.0 KiB
Transact-SQL
72 lines
2.0 KiB
Transact-SQL
BEGIN TRY
|
|
|
|
BEGIN TRAN;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE [dbo].[campus] (
|
|
[id] INT NOT NULL IDENTITY(1,1),
|
|
[nom] NVARCHAR(1000) NOT NULL,
|
|
[adresse] NVARCHAR(1000) NOT NULL,
|
|
[ville] NVARCHAR(1000) NOT NULL,
|
|
[code_postal] NVARCHAR(1000) NOT NULL,
|
|
[actif] BIT NOT NULL,
|
|
[created_at] DATETIME2 NOT NULL,
|
|
[updated_at] DATETIME2 NOT NULL,
|
|
CONSTRAINT [campus_pkey] PRIMARY KEY CLUSTERED ([id])
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE [dbo].[role] (
|
|
[id] INT NOT NULL IDENTITY(1,1),
|
|
[code] NVARCHAR(1000) NOT NULL,
|
|
[libelle] NVARCHAR(1000) NOT NULL,
|
|
[description] NVARCHAR(1000) NOT NULL,
|
|
CONSTRAINT [role_pkey] PRIMARY KEY CLUSTERED ([id])
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE [dbo].[utilisateur] (
|
|
[id] INT NOT NULL IDENTITY(1,1),
|
|
[microsoft_id] NVARCHAR(1000) NOT NULL,
|
|
[nom] NVARCHAR(1000) NOT NULL,
|
|
[prenom] NVARCHAR(1000) NOT NULL,
|
|
[email] NVARCHAR(1000) NOT NULL,
|
|
[classe] NVARCHAR(1000) NOT NULL,
|
|
[role_id] INT NOT NULL,
|
|
[campus_id] INT NOT NULL,
|
|
[actif] BIT NOT NULL,
|
|
[created_at] DATETIME2 NOT NULL,
|
|
[updated_at] DATETIME2 NOT NULL,
|
|
CONSTRAINT [utilisateur_pkey] PRIMARY KEY CLUSTERED ([id])
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE [dbo].[categorie_materiel] (
|
|
[id] INT NOT NULL IDENTITY(1,1),
|
|
[nom] NVARCHAR(1000) NOT NULL,
|
|
[description] NVARCHAR(1000) NOT NULL,
|
|
[actif] BIT NOT NULL,
|
|
[created_at] DATETIME2 NOT NULL,
|
|
[updated_at] DATETIME2 NOT NULL,
|
|
CONSTRAINT [categorie_materiel_pkey] PRIMARY KEY CLUSTERED ([id])
|
|
);
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE [dbo].[utilisateur] ADD CONSTRAINT [utilisateur_role_id_fkey] FOREIGN KEY ([role_id]) REFERENCES [dbo].[role]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE [dbo].[utilisateur] ADD CONSTRAINT [utilisateur_campus_id_fkey] FOREIGN KEY ([campus_id]) REFERENCES [dbo].[campus]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;
|
|
|
|
COMMIT TRAN;
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
|
|
IF @@TRANCOUNT > 0
|
|
BEGIN
|
|
ROLLBACK TRAN;
|
|
END;
|
|
THROW
|
|
|
|
END CATCH
|