Modification d'une table

bonjour,

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

Code:

---

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:

---

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.

Citation:

---

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

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?

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

Citation:

---

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:

---

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:

---

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:

---

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:

---

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:

---

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

---

@++ ;)

Citation:

---

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 :mouarf:

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

Citation:

---

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:

---

'PR' + REPLICATE('0', 10 - LEN(CAST(@ProjectID AS VARCHAR(10)))) + CAST(@ProjectID AS VARCHAR(10))

---

@++ ;)

Citation:

---

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.

Citation:

---

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 ? :D

@++ ;)

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:

---

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:

---

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.

Citation:

---

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 :

Citation:

---

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.

Citation:

---

1) ajout du champ avec null autorisé

---

Code:

---

1 2	ALTER TABLE dbo.Project ADD ProjectNumber VARCHAR(50) NULL

---

Citation:

---

2) UPDATE Project SET ProjectNumber = 'PR' + CAST(ProjectID as varchar(10))

---

Cette requête est utilisable directement.

Citation:

---

3) modifier le champ pour que le null soit interdit

---

Code:

---

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:

---

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

@++ ;)

Pour vérification, voilà ce que donne la déclaration de ma table "Project" après modification:

Code:

---

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!

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.

@++ ;)