IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Modification d'une table


Sujet :

Développement SQL Server

  1. #1
    Membre habitué
    Profil pro
    Inscrit en
    Janvier 2004
    Messages
    488
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Janvier 2004
    Messages : 488
    Points : 134
    Points
    134
    Par défaut Modification d'une table
    bonjour,

    J'ai une table projet dans ma base de donnée sql serveur 2008.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
    CREATE TABLE [dbo].[Project](
    	[ProjectID] [int] IDENTITY(1,1) NOT NULL,
    	[PoleID] [int] NOT NULL,
    	[ProjectManagerID] [nvarchar](50) NOT NULL,
    	[Title] [varchar](50) NOT NULL,
    	[Customer] [varchar](50) NOT NULL,
     CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED 
    (
    	[ProjectID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    1) J'aimerai y rajouter une colonne "NumberProject" en varchar(50) not null

    Voilà comment je procède, mais sql serveur refuse d'affecter la valeur not null à mon champs! Pourquoi?

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     
    ALTER TABLE Project
    ADD ProjectNumber varchar(50) not null
    2) Ma table "Project" contient déjà des enregistrements, j'aimerai après avoir créer la colonne NumberProject, remplir la automatiquement de la façon suivante:

    Pour le Projet 1, NumberProject = PR1
    Pour le Projet 2, NumberProject = PR2
    Pour le Projet 3, NumberProject = PR3
    En incrémentale ...

    Je précise que le champs "NumberProject" est unique...comment gérer ça? Dois-je en faire une clés primaire? si oui, comment gérer alors ma clés primaire ProjectID???

    Merci d'avance pour vos réponses.

  2. #2
    Modérateur

    Homme Profil pro
    Développeur java, access, sql server
    Inscrit en
    Octobre 2005
    Messages
    2 705
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Développeur java, access, sql server
    Secteur : Industrie

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 705
    Points : 4 783
    Points
    4 783
    Par défaut
    sql serveur refuse d'affecter la valeur not null à mon champs! Pourquoi?
    Au moment de l'ajout de la colonne, les valeurs 'PR1, PR2, ...' ne sont pas encore dans le nouveau champ. Il contient donc null.

    Le plus simple est de le faire en 3 étapes :

    1) ajout du champ avec null autorisé
    2) UPDATE Project SET ProjectNumber = 'PR' + CAST(ProjectID as varchar(10))
    3) modifier le champ pour que le null soit interdit
    Labor improbus omnia vincit un travail acharné vient à bout de tout - Ambroise Paré (1510-1590)

    Consulter sans modération la FAQ ainsi que les bons ouvrages : http://jmdoudoux.developpez.com/cours/developpons/java/

  3. #3
    Membre habitué
    Profil pro
    Inscrit en
    Janvier 2004
    Messages
    488
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Janvier 2004
    Messages : 488
    Points : 134
    Points
    134
    Par défaut
    tout d'abord merci pour votre réponse!

    1) ok pour le not null non autorisé quand le champs est vide

    2) Le cast ne se fait pas sur le champs ProjectID, je veux juste qu'il incrémente de 1 à chaque fois, comment faire?

    3) comment gérer l'unicité de ma colonne ProjectNumber?

  4. #4
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    Bonjour,

    Plusieurs petites erreurs de vocabulaire :
    - NULL n'est pas une valeur : c'est l'absence de valeur.
    - en base de données il n'y a pas d'enregistrements, mais des lignes

    Je précise que le champs "NumberProject" est unique...comment gérer ça? Dois-je en faire une clés primaire? si oui, comment gérer alors ma clés primaire ProjectID???
    Une table ne peut avoir qu'une seule clé primaire, sinon il est impossible d'identifier de façon directe une ligne de la table.
    En revanche vous pouvez ajouter une contrainte d'unicité :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    ALTER TABLE Project
    ADD CONSTRAINT UQ_Project_ProjectNumber UNIQUE (ProjectNumber)
    Vous pouvez également remplir la colonne à l'aide d'une fonction couplée à une contrainte de valeur par défaut :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    CREATE FUNCTION dbo.Fn_GetProjetNumber
    	(@ProjectID INT)
    	RETURNS VARCHAR(10)
    	WITH SCHEMABINDING
    AS
    BEGIN
    	RETURN ('PR' + CAST(@ProjectID AS VARCHAR(10)))
    END
    Et dès lors vous pouvez écrire :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    ALTER TABLE Project
    ADD ProjectNumber varchar(50) NOT NULL CONSTRAINT DF_Project_ProjectNumber DEFAULT (dbo.Fn_GetProjetNumber(ProjectID))
    Sans avoir besoin donc de passer par les trois étapes que vous a préconisé Népomucène.

    Une autre solution est de rajouter une colonne calculée, soit en vous servant de la fonction :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    ALTER TABLE dbo.Project
    ADD ProjectNumber AS (dbo.Fn_GetProjetNumber(ProjectID)) PERSISTED NOT NULL
    GO
     
    ALTER TABLE dbo.Project
    ADD CONSTRAINT UQ_Project_ProjectNumber UNIQUE (ProjectNumber)
    GO
    Ou sans fonction :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    ALTER TABLE dbo.Project
    ADD ProjectNumber AS ('PR' + CAST(@ProjectID AS VARCHAR(10))) PERSISTED NOT NULL
    GO
     
    ALTER TABLE dbo.Project
    ADD CONSTRAINT UQ_Project_ProjectNumber UNIQUE (ProjectNumber)
    GO
    @++

  5. #5
    Modérateur

    Homme Profil pro
    Développeur java, access, sql server
    Inscrit en
    Octobre 2005
    Messages
    2 705
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Développeur java, access, sql server
    Secteur : Industrie

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 705
    Points : 4 783
    Points
    4 783
    Par défaut
    je veux juste qu'il incrémente de 1 à chaque fois
    Il faut le faire par un trigger "FOR INSERT"
    qui va prendre la valeur max de ProjectNumber (tu as intérêt à le formatter PR0001, PR0002, ....)
    ensuite extraire la partie numérique (substring + Cast as int )
    ajouter +1
    et enfin mettre à jour ce foutu champ

    C'est le principe général.
    Si tu as des difficultés de réalisation, renvoie un post

    ah tient, mon post s'est croisé avec le précédent de elsuket
    Labor improbus omnia vincit un travail acharné vient à bout de tout - Ambroise Paré (1510-1590)

    Consulter sans modération la FAQ ainsi que les bons ouvrages : http://jmdoudoux.developpez.com/cours/developpons/java/

  6. #6
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    je veux juste qu'il incrémente de 1 à chaque fois, comment faire?
    Votre colonne étant auto-incrémentée, il n'y a pas de problème, puisque vous pouvez soit mettre votre colonne comme calculée, soit lui adjoindre une contrainte de valeur par défaut.

    Supposons que votre numéro de projet aie une largeur fixe de 10 caractères.
    Il suffit alors d'utiliser la fonction REPLICATE :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    'PR' + REPLICATE('0', 10 - LEN(CAST(@ProjectID AS VARCHAR(10)))) + CAST(@ProjectID AS VARCHAR(10))
    @++

  7. #7
    Modérateur

    Homme Profil pro
    Développeur java, access, sql server
    Inscrit en
    Octobre 2005
    Messages
    2 705
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Développeur java, access, sql server
    Secteur : Industrie

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 705
    Points : 4 783
    Points
    4 783
    Par défaut
    Votre colonne étant auto-incrémentée, il n'y a pas de problème
    C'est pas tout à fait sûr...
    Comme la table existe déjà, il peut y avoir des trous dans la numérotation (projets supprimés).
    Il me semble qu'il s'agit d'une codification métier des projets qui ne suivra peut-être pas l'auto-incrément.
    J'ai le cas chez un client qui ré-initialise ses numéros de dossiers tous les mois.

    La solution de elsuket de créer une fonction qui sert de valeur par défaut est élégante et permet de se passer du trigger.
    Labor improbus omnia vincit un travail acharné vient à bout de tout - Ambroise Paré (1510-1590)

    Consulter sans modération la FAQ ainsi que les bons ouvrages : http://jmdoudoux.developpez.com/cours/developpons/java/

  8. #8
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    Comme la table existe déjà, il peut y avoir des trous dans la numérotation (projets supprimés).
    C'est exact, je n'y ai pas pensé.
    Néanmoins supposons que nous créons à la suite des nos "trous" tous les projets avec un n° de projet toujours auto-incrémenté : si nous supprimons un projet, nous perdons également son n°.

    Le but est ici d'identifier le projet de façon déterministe, mais il est tout çà fait vrai qu'il peut y avoir des contraintes métier qui imposent une numérotation.
    Si tel est le cas, un recours à une table extérieure pour stocker la valeur du compteur sera nécessaire, et un recours à une procédure stockée qui incrémente ou décrémente ce compteur devra être couplée à un trigger INSTEAD OF INSERT, DELETE ...

    Y'a-t-il donc une règle métier ?

    @++

  9. #9
    Membre habitué
    Profil pro
    Inscrit en
    Janvier 2004
    Messages
    488
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Janvier 2004
    Messages : 488
    Points : 134
    Points
    134
    Par défaut
    1) Pour la gestion de la colonne, j'ai utilisé votre méthode sans la fonction, à savoir celle-ci ( mais sans le @ devant ProjectID, ça ne fonctionnait pas sinon):

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    ALTER TABLE dbo.Project
    ADD ProjectNumber AS ('PR' + CAST(ProjectID AS VARCHAR(10))) PERSISTED NOT NULL
    GO
     
    ALTER TABLE dbo.Project
    ADD CONSTRAINT UQ_Project_ProjectNumber UNIQUE (ProjectNumber)
    GO
    oupsss...voilà ce que j'obtiens maintenant dans la déclaration de ma table:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    CREATE TABLE [dbo].[Project](
    	[ProjectID] [int] IDENTITY(1,1) NOT NULL,
    	[StatusID] [int] NOT NULL,
    	[PoleID] [int] NOT NULL,
    	[ProjectManagerID] [nvarchar](50) NOT NULL,
    	[ProjectNumber]  AS ('PR'+CONVERT([varchar](10),[ProjectID],0)) PERSISTED NOT NULL,
     CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED 
    (
    	[ProjectID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
     CONSTRAINT [UQ_Project_ProjectNumber] UNIQUE NONCLUSTERED 
    (
    	[ProjectNumber] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    Je voulais que ma colonne "ProjectNumber" ne se remplisse automatiquement que cette fois-ci, elle sera remplit par saisie sinon. Je crains qu'elle ne se remplisse automatiquement à chaque fois maintenant??

    2) Quelle est l'intérêt d'utiliser votre fonction dans mon cas? et pourriez vous m'expliquer ce qu'est réellement une fonction couplée à une contrainte de valeur par défaut? Pardonnez moi mais ce n'est pas très clair pour moi.

    3) Concernant la fonction "replicate", il faut que je la rajoute dans un update?

    Merci pour vos réponses et vos explications.

  10. #10
    Modérateur

    Homme Profil pro
    Développeur java, access, sql server
    Inscrit en
    Octobre 2005
    Messages
    2 705
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Développeur java, access, sql server
    Secteur : Industrie

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 705
    Points : 4 783
    Points
    4 783
    Par défaut
    Je voulais que ma colonne "ProjectNumber" ne se remplisse automatiquement que cette fois-ci, elle sera remplit par saisie sinon
    Il faut revenir à la première solution :
    1) ajout du champ avec null autorisé
    2) UPDATE Project SET ProjectNumber = 'PR' + CAST(ProjectID as varchar(10))
    3) modifier le champ pour que le null soit interdit
    comme ça l'utilisateur est obligé de saisir le numéro de projet

    C'est vrai que cela utilise ProjectID comme numérotation.
    Dis-nous si cela est un vrai problème, on peut imaginer un bricolage.
    Labor improbus omnia vincit un travail acharné vient à bout de tout - Ambroise Paré (1510-1590)

    Consulter sans modération la FAQ ainsi que les bons ouvrages : http://jmdoudoux.developpez.com/cours/developpons/java/

  11. #11
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    1) ajout du champ avec null autorisé
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    ALTER TABLE dbo.Project
    ADD ProjectNumber VARCHAR(50) NULL
    2) UPDATE Project SET ProjectNumber = 'PR' + CAST(ProjectID as varchar(10))
    Cette requête est utilisable directement.

    3) modifier le champ pour que le null soit interdit
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    ALTER TABLE dbo.Project
    ADD ProjectNumber VARCHAR(50) NOT NULL
    Et pour la gestion de l'unicité de vos codes de projet :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    CREATE UNIQUE INDEX IXUQF_Project_ProjectNumber
    ON dbo.Project(ProjectNumber)
    WHERE ProjectNumber IS NOT NULL
    Cette dernière requête vous permet de rajouter un index unique pour toutes les valeurs de votre colonne, sauf celles qui sont à NULL.
    De cette manière, plusieurs projets peuvent rester sans n° de projet

    @++

  12. #12
    Membre habitué
    Profil pro
    Inscrit en
    Janvier 2004
    Messages
    488
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Janvier 2004
    Messages : 488
    Points : 134
    Points
    134
    Par défaut
    Pour vérification, voilà ce que donne la déclaration de ma table "Project" après modification:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
     
    CREATE TABLE [dbo].[Project](
    	[ProjectID] [int] IDENTITY(1,1) NOT NULL,
    	[StatusID] [int] NOT NULL,
    	[PoleID] [int] NOT NULL,
    	[ProjectManagerID] [nvarchar](50) NOT NULL,
    	[ProjectNumber] [varchar](50) NOT NULL,
     CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED 
    (
    	[ProjectID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
     CONSTRAINT [UQ_Project_ProjectNumber] UNIQUE NONCLUSTERED 
    (
    	[ProjectNumber] 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
    Merci pour toutes vos explications.

    Le fait de remplir la ligne "ProjectNumber" sur le ProjectID ne pose pas de problème pour l'instant, je reviens vers vous si ça change.

    Merci!

  13. #13
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    Ce script est tout à fait normal :

    - la création d'une contrainte de clé primaire entraîne implicitement la création d'une index cluster donc la clé est composée des colonnes qui constituent la clé primaire
    - la création d'une contrainte d'unicité entraîne la création d'un index unique non cluster, sauf si cela est spécifié explicitement à l'aide de la clause CLUSTERED, et que la table n'a pas de clé primaire.

    @++

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Ajout & modif d'une table vers l'autre
    Par BOTIGUA dans le forum Access
    Réponses: 1
    Dernier message: 17/02/2006, 19h00
  2. Tracer les modifications d'une table
    Par vinze11 dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 17/02/2006, 14h44
  3. Vue non mise à jour après modification d'une table
    Par cybernet35 dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 19/01/2006, 14h54
  4. date de dernière modification d'une table ?
    Par NiBicUs dans le forum Requêtes
    Réponses: 3
    Dernier message: 17/12/2004, 19h11
  5. Erreur lors de modification d'une table
    Par seb.49 dans le forum SQL
    Réponses: 11
    Dernier message: 13/01/2003, 18h16

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo