none
SQL Server - création d'une contrainte à l'insertion d'une nouvelle donnée RRS feed

  • Question

  • Bonjour,

     

    Voici mon problème :

    J'ai une base données qui contient du matériel et son utilisation/affectation.

    J'ai besoin de mettre en place des contraintes. Par exemple, une prise réseau ne doit pas pouvoir être affectée à plusieurs utilisateurs, par contre, un PC peut être associé à plusieurs personnes. Comment mettre en place cette contrainte ?

     

    vendredi 22 juillet 2011 14:53

Réponses

  • Bonjour,

    Peut-être avec un trigger pour vérifier si le matériel est "personnel" et dans ce cas que le matériel n'apparait bien qu'une fois ? Qq chose comme :

    create table UM(u varchar(10),m varchar(10))
    go
    create trigger mytrigger on UM for insert,update as
    if exists(select 1 from UM where m in (select m from inserted) and m like 'p%' group by m having COUNT(*)>1)
    	rollback transaction
    go
    insert into um values('u1','m1')
    insert into um values('u2','m1')
    insert into um values('u1','p1')
    insert into um values('u2','p1')
    

    Dans ce cas je considère que le matériel dont le nom commence par p doit n'apparaitre qu'une fois. Bien sûr en réalité en irait sans doute chercher une colonne dans le "Type du matériel" et qui est indique si ce matériel est personnel ou partagé.

    Sinon on sépare les deux (avec par exemple une table pour le matériel personnel avec un champ utilisateur et qui n'utiliserait pas la table d'association ce qui permettrait de se passer du trigger).


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    samedi 30 juillet 2011 16:52
  • Bonjour,

     

    La réponse de Patrice est la bonne. Adapté à ma base, cela donne donc :

     

    create trigger MonTrigger on dbo.AssoUtilisateursMateriel for insert,update as

    if exists(select NomMateriel from dbo.AssoUtilisateursMateriel where NomMateriel in (select NomMateriel from inserted) and NomMateriel like 'Prise%' group by NomMateriel having COUNT(*)>1)

          rollback transaction

    go

     

     

     

    Merci Patrice ! :)

     

     

    mercredi 3 août 2011 16:06

Toutes les réponses

  • Histoire de, les commandes ci-dessous permettent de créer une base avec 4 tables, quelques données et des relations. L'idée est de, si possible, me fournir un exemple qui fasse en sorte qu'il ne soit pas possible d'affecter une prise de plus à un utilisateur.

     

    Christophe

     

     

    USE [master]

    GO

    /****** Objet :  Database [mabase]    Date de génération du script : 07/22/2011 15:42:32 ******/

    CREATE DATABASE [mabase]

    GO

    ALTER DATABASE [mabase] SET ANSI_NULL_DEFAULT OFF

    GO

    ALTER DATABASE [mabase] SET ANSI_NULLS OFF

    GO

    ALTER DATABASE [mabase] SET ANSI_PADDING OFF

    GO

    ALTER DATABASE [mabase] SET ANSI_WARNINGS OFF

    GO

    ALTER DATABASE [mabase] SET ARITHABORT OFF

    GO

    ALTER DATABASE [mabase] SET AUTO_CLOSE OFF

    GO

    ALTER DATABASE [mabase] SET AUTO_CREATE_STATISTICS ON

    GO

    ALTER DATABASE [mabase] SET AUTO_SHRINK OFF

    GO

    ALTER DATABASE [mabase] SET AUTO_UPDATE_STATISTICS ON

    GO

    ALTER DATABASE [mabase] SET CURSOR_CLOSE_ON_COMMIT OFF

    GO

    ALTER DATABASE [mabase] SET CURSOR_DEFAULT  GLOBAL

    GO

    ALTER DATABASE [mabase] SET CONCAT_NULL_YIELDS_NULL OFF

    GO

    ALTER DATABASE [mabase] SET NUMERIC_ROUNDABORT OFF

    GO

    ALTER DATABASE [mabase] SET QUOTED_IDENTIFIER OFF

    GO

    ALTER DATABASE [mabase] SET RECURSIVE_TRIGGERS OFF

    GO

    ALTER DATABASE [mabase] SET  ENABLE_BROKER

    GO

    ALTER DATABASE [mabase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

    GO

    ALTER DATABASE [mabase] SET DATE_CORRELATION_OPTIMIZATION OFF

    GO

    ALTER DATABASE [mabase] SET TRUSTWORTHY OFF

    GO

    ALTER DATABASE [mabase] SET ALLOW_SNAPSHOT_ISOLATION OFF

    GO

    ALTER DATABASE [mabase] SET PARAMETERIZATION SIMPLE

    GO

    ALTER DATABASE [mabase] SET  READ_WRITE

    GO

    ALTER DATABASE [mabase] SET RECOVERY FULL

    GO

    ALTER DATABASE [mabase] SET  MULTI_USER

    GO

    ALTER DATABASE [mabase] SET PAGE_VERIFY CHECKSUM

    GO

    ALTER DATABASE [mabase] SET DB_CHAINING OFF

    GO

    USE [mabase]

    GO

    /****** Objet :  Table [dbo].[Utilisateurs]    Date de génération du script : 07/22/2011 15:42:32 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[Utilisateurs](

          [Nom] [nvarchar](100) NOT NULL,

     CONSTRAINT [PK_Utilisateurs] PRIMARY KEY CLUSTERED

    (

          [Nom] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    /****** Objet :  Table [dbo].[TypeMateriel]    Date de génération du script : 07/22/2011 15:42:32 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[TypeMateriel](

          [Type] [nvarchar](50) NOT NULL,

     CONSTRAINT [PK_TypeMateriel] PRIMARY KEY CLUSTERED

    (

          [Type] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    /****** Objet :  Table [dbo].[AssoUtilisateursMateriel]    Date de génération du script : 07/22/2011 15:42:32 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[AssoUtilisateursMateriel](

          [NomUtilisateur] [nvarchar](100) NOT NULL,

          [NomMateriel] [nvarchar](50) NOT NULL,

     CONSTRAINT [PK_AssoUtilisateursMateriel] PRIMARY KEY CLUSTERED

    (

          [NomUtilisateur] ASC,

          [NomMateriel] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    /****** Objet :  Table [dbo].[Materiel]    Date de génération du script : 07/22/2011 15:42:32 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[Materiel](

          [Nom] [nvarchar](50) NOT NULL,

          [Type] [nvarchar](50) NOT NULL,

     CONSTRAINT [PK_Materiel_1] PRIMARY KEY CLUSTERED

    (

          [Nom] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    /****** Objet :  ForeignKey [FK_AssoUtilisateursMateriel_Materiel]    Date de génération du script : 07/22/2011 15:42:32 ******/

    ALTER TABLE [dbo].[AssoUtilisateursMateriel]  WITH CHECK ADD  CONSTRAINT [FK_AssoUtilisateursMateriel_Materiel] FOREIGN KEY([NomMateriel])

    REFERENCES [dbo].[Materiel] ([Nom])

    ON UPDATE CASCADE

    ON DELETE CASCADE

    GO

    ALTER TABLE [dbo].[AssoUtilisateursMateriel] CHECK CONSTRAINT [FK_AssoUtilisateursMateriel_Materiel]

    GO

    /****** Objet :  ForeignKey [FK_AssoUtilisateursMateriel_Utilisateurs]    Date de génération du script : 07/22/2011 15:42:32 ******/

    ALTER TABLE [dbo].[AssoUtilisateursMateriel]  WITH CHECK ADD  CONSTRAINT [FK_AssoUtilisateursMateriel_Utilisateurs] FOREIGN KEY([NomUtilisateur])

    REFERENCES [dbo].[Utilisateurs] ([Nom])

    ON UPDATE CASCADE

    ON DELETE CASCADE

    GO

    ALTER TABLE [dbo].[AssoUtilisateursMateriel] CHECK CONSTRAINT [FK_AssoUtilisateursMateriel_Utilisateurs]

    GO

    /****** Objet :  ForeignKey [FK_Materiel_TypeMateriel]    Date de génération du script : 07/22/2011 15:42:32 ******/

    ALTER TABLE [dbo].[Materiel]  WITH CHECK ADD  CONSTRAINT [FK_Materiel_TypeMateriel] FOREIGN KEY([Type])

    REFERENCES [dbo].[TypeMateriel] ([Type])

    ON UPDATE CASCADE

    ON DELETE CASCADE

    GO

    ALTER TABLE [dbo].[Materiel] CHECK CONSTRAINT [FK_Materiel_TypeMateriel]

    GO

     

    INSERT INTO [mabase].[dbo].[Utilisateurs] ([Nom]) VALUES ('DUPONT Pierre')

    INSERT INTO [mabase].[dbo].[Utilisateurs] ([Nom]) VALUES ('LAJOIE Fred')

    INSERT INTO [mabase].[dbo].[Utilisateurs] ([Nom]) VALUES ('LAMAISON Julius')

     

    INSERT INTO [mabase].[dbo].[TypeMateriel] ([Type]) VALUES ('PC')

    INSERT INTO [mabase].[dbo].[TypeMateriel] ([Type]) VALUES ('Prise')

     

    INSERT INTO [mabase].[dbo].[Materiel] ([Nom],[Type]) VALUES ('PC1','PC')

    INSERT INTO [mabase].[dbo].[Materiel] ([Nom],[Type]) VALUES ('PC2','PC')

    INSERT INTO [mabase].[dbo].[Materiel] ([Nom],[Type]) VALUES ('PC3','PC')

    INSERT INTO [mabase].[dbo].[Materiel] ([Nom],[Type]) VALUES ('PC4','PC')

    INSERT INTO [mabase].[dbo].[Materiel] ([Nom],[Type]) VALUES ('Prise1','Prise')

    INSERT INTO [mabase].[dbo].[Materiel] ([Nom],[Type]) VALUES ('Prise2','Prise')

    INSERT INTO [mabase].[dbo].[Materiel] ([Nom],[Type]) VALUES ('Prise3','Prise')

    INSERT INTO [mabase].[dbo].[Materiel] ([Nom],[Type]) VALUES ('Prise4','Prise')

     

    INSERT INTO [mabase].[dbo].[AssoUtilisateursMateriel] ([NomUtilisateur],[NomMateriel]) VALUES ('DUPONT Pierre','PC1')

    INSERT INTO [mabase].[dbo].[AssoUtilisateursMateriel] ([NomUtilisateur],[NomMateriel]) VALUES ('DUPONT Pierre','Prise1')

    INSERT INTO [mabase].[dbo].[AssoUtilisateursMateriel] ([NomUtilisateur],[NomMateriel]) VALUES ('LAJOIE Fred','PC2')

    INSERT INTO [mabase].[dbo].[AssoUtilisateursMateriel] ([NomUtilisateur],[NomMateriel]) VALUES ('LAJOIE Fred','Prise2')

    INSERT INTO [mabase].[dbo].[AssoUtilisateursMateriel] ([NomUtilisateur],[NomMateriel]) VALUES ('LAMAISON Julius','PC3')

    INSERT INTO [mabase].[dbo].[AssoUtilisateursMateriel] ([NomUtilisateur],[NomMateriel]) VALUES ('LAMAISON Julius','Prise3')

     

     

    vendredi 22 juillet 2011 14:54
  • Bonjour,

    Peut-être avec un trigger pour vérifier si le matériel est "personnel" et dans ce cas que le matériel n'apparait bien qu'une fois ? Qq chose comme :

    create table UM(u varchar(10),m varchar(10))
    go
    create trigger mytrigger on UM for insert,update as
    if exists(select 1 from UM where m in (select m from inserted) and m like 'p%' group by m having COUNT(*)>1)
    	rollback transaction
    go
    insert into um values('u1','m1')
    insert into um values('u2','m1')
    insert into um values('u1','p1')
    insert into um values('u2','p1')
    

    Dans ce cas je considère que le matériel dont le nom commence par p doit n'apparaitre qu'une fois. Bien sûr en réalité en irait sans doute chercher une colonne dans le "Type du matériel" et qui est indique si ce matériel est personnel ou partagé.

    Sinon on sépare les deux (avec par exemple une table pour le matériel personnel avec un champ utilisateur et qui n'utiliserait pas la table d'association ce qui permettrait de se passer du trigger).


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    samedi 30 juillet 2011 16:52
  • Bonjour, ChrisFromParis,

    Est-ce que vous avez pu avancer en utilisant les informations fournies par Patrice ? Merci de partager avec nous les résultats, afin que d'autres personnes avec le même problème puissent profiter de cette solution.

     

    Cordialement,

    Cipri


    Suivez MSDN sur Twitter   Suivez MSDN sur Facebook


    Ciprian DUDUIALA, MSFT  
    •Nous vous prions de considérer que dans le cadre de ce forum on n’offre pas de support technique et aucune garantie de la part de Microsoft ne peut être offerte.


    mardi 2 août 2011 09:48
  • Bonjour,

     

    La réponse de Patrice est la bonne. Adapté à ma base, cela donne donc :

     

    create trigger MonTrigger on dbo.AssoUtilisateursMateriel for insert,update as

    if exists(select NomMateriel from dbo.AssoUtilisateursMateriel where NomMateriel in (select NomMateriel from inserted) and NomMateriel like 'Prise%' group by NomMateriel having COUNT(*)>1)

          rollback transaction

    go

     

     

     

    Merci Patrice ! :)

     

     

    mercredi 3 août 2011 16:06
  • Bonjour,

     

    Merci de tenir la communauté informée sur la suite de vos démarches.

     

    Bonne journée,

     

    Cipri


    Suivez MSDN sur Twitter   Suivez MSDN sur Facebook


    Ciprian DUDUIALA, MSFT  
    •Nous vous prions de considérer que dans le cadre de ce forum on n’offre pas de support technique et aucune garantie de la part de Microsoft ne peut être offerte.

    jeudi 4 août 2011 07:37