[9i] Contrainte NOT NULL

Version imprimable

Voir 40 message(s) de cette discussion en une page

Bonjour,

Je voudrais modifier une table pour lui mettre la contrainte NOT NULL sur une colonne, mais j'ai un message d'erreur.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 SQL> create table liste ( 2 ipn varchar2(3) 3 , itl number(2) 4 ) 5 / Table created. SQL> alter table liste add constraint nn_ipn not null (ipn) 2 / alter table liste add constraint nn_ipn not null (ipn) * ERROR at line 1: ORA-00904: : invalid identifier
Ma question est: est-il possible mettre une contrainte NOT NULL après la création de la table (comme c'est possible pour la PK, la FK, la contrainte CHECK) ? Si oui, quelle est la bonne syntaxe ? Sinon, faut-il nécessairement la mettre à la création :

Citation:

create table liste (
ipn varchar2(3) constraint nn_ipn not null
, itl number(2)
)
/

Merci d'avance.

01/02/2007, 17h20
rouardg

Code:

ALTER TABLE liste modify (ipn not null) ;
01/02/2007, 17h43
Ito

Citation:

Envoyé par rouardg

Code:

ALTER TABLE liste modify (ipn not null) ;

J'ai testé et ça marche. Mais, je voudrais nommer cette contrainte ('nn_ipn') est-ce possible, si oui comment ?

Merci.
01/02/2007, 18h08
rouardg

Code:

ALTER TABLE liste ADD constraint nn_ipn check (ipn is not null) ;

Mais je ne te le conseille pas. En effet, quand tu en seras à 250 ième table, et à nommer la 1000 ième contrainte de type NOT NULL, je pense que tu ressentiras une certaine lassitude.
02/02/2007, 10h05
Ito

Citation:

Envoyé par rouardg

Code:

ALTER TABLE liste ADD constraint nn_ipn check (ipn is not null) ;

Mais je ne te le conseille pas. En effet, quand tu en seras à 250 ième table, et à nommer la 1000 ième contrainte de type NOT NULL, je pense que tu ressentiras une certaine lassitude.

Merci.
05/02/2007, 11h04
laurentschneider

Citation:

Envoyé par rouardg

Code:

ALTER TABLE liste ADD constraint nn_ipn check (ipn is not null) ;

ce n'est pas pareil, car la colonne ne sera pas affichée comme non-nulle dans un DESC (user_tab_columns.nullable='N').
05/02/2007, 14h56
Ito

Citation:

Envoyé par laurentschneider

ce n'est pas pareil, car la colonne ne sera pas affichée comme non-nulle dans un DESC (user_tab_columns.nullable='N').

Bonjour,

Je ne saisis pas la nuance, peux-tu m'expliquer ?

Merci.
05/02/2007, 16h43
laurentschneider
Lorsque tu fais
Code:

1 2 3 SQL> desc t C NOT NULL NUMBER
tu dois voir les colonnes définies comme non-nulles. Par contre, si tu définis une contrainte CHECK, par exemple CHECK C IS NOT NULL, Oracle ne considérera pas la colonne comme NOT NULL.

Outre l'affichage de DESCRIBE et de USER_TAB_COLUMNS, il est possible d'avoir que l'optimiseur trouve de meilleures plans avec des colonnes NOT NULL, qu'avec des colonnes ayant des contraintes CHECK (C IS NOT NULL)

OK?
05/02/2007, 16h59
laurentschneider

un autre effet est la persistence des colonnes NOT NULL et la non-persistence des contraintes CHECK

C'est à dire, si tu fais

Code:

CREATE TABLE T2 as SELECT * FROM T1;

Les colonnes NOT NULL de T1 seront NOT NULL dans T2, mais aucune autre contrainte de T1 ne sera copiée dans T2.

Vu?
05/02/2007, 17h30
pifor

Ceci dit, on peut aussi penser que nommer les contraintes a aussi des avantages:http://asktom.oracle.com/pls/asktom/...#8960434782023

Citation:

clarity,
meaning,
documentation,
readability in the data dictionary,
more meaningful information from error messages...
06/02/2007, 07h41
laurentschneider

Je ne suis pas d'accord avec "readability in the data dictionary", attendu que USER_TAB_COLUMNS.NULLABLE n'est pas mis à jour
08/02/2007, 11h16
Ito

Bonjour laurentschneider,

Je saisis maintenant la nuance et dans TOAD j'ai vu la différence dans l'onglet "Columns" dans l'affichage des paramètres des colonnes de la table:
1) quand on crée la contrainte en même temps que la table, pour le "Null?" la réponse est "N"
2) quand on ajoute la contrainte par un ALTER, pour le "Null?" la réponse est "Y".
Du coup je vais finalement garder le 1er cas en nommant la contrainte.

Merci pour tes éclaircissements.
08/02/2007, 13h38
McM

Citation:

Envoyé par Ito

1) quand on crée la contrainte en même temps que la table, pour le "Null?" la réponse est "N"
2) quand on ajoute la contrainte par un ALTER, pour le "Null?" la réponse est "Y".

C'est plutôt qu'une contrainte NOT NULL mettra le champ "Null?" à "N" alors qu'une contrainte CHECK ne fera rien.
Tu peux très bien créer ta contrainte not null après la création de Table

Code:

ALTER TABLE liste MODIFY (ipn NOT NULL) ;
09/02/2007, 12h25
Ito

Citation:

Envoyé par McM

Tu peux très bien créer ta contrainte not null après la création de Table

Code:

ALTER TABLE liste MODIFY (ipn NOT NULL) ;

Bonjour,

Tu as raison et c'est souligné plus haut, sauf que dans ce cas je ne peux pas nommer la contrainte d'où la solution retenue évoquée ci-dessus en tenant compte des remarques de laurentschneider.

Cdlt.

tu peux toujours renommer la contrainte

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
SQL> create table t (x number not null);
 
Table created.
 
SQL> select constraint_name from user_cons_columns where column_name='X' and table_name='T';
CONSTRAINT_NAME
------------------------------
SYS_C003709
 
SQL> alter table t rename constraint SYS_C003709 to X_NOT_NULL;
 
Table altered.
 
SQL> desc t
 Name              Null?    Type
 ----------------- -------- ------------
 X                 NOT NULL NUMBER

09/02/2007, 16h03
Ito

Citation:

Envoyé par laurentschneider

tu peux toujours renommer la contrainte

Ok, sauf que je fais 1 étape supplémentaire (chercher la contrainte)...
09/02/2007, 16h05
laurentschneider

:pingoin2: et ça te pose un problème ?
12/02/2007, 12h52
Ito

Citation:

Envoyé par laurentschneider

:pingoin2: et ça te pose un problème ?

Bonjour,

Techniquement non, mais ça modifie l'organisation de mon travail. Je travaille de la manière suivante: pour optimiser le temps de chargement, le principe est que je crée les contraintes nominatives après le chargement des tables et pas pendant (sauf pour la contrainte NOT NULL) ou après la création de la structure de la table. Les scripts de création des contraintes sont dans un fichier qui est lancé après le chargement des données, ce fichier ne contient pas autre chose que des ALTER TABLE .... ADD CONSTRAINT etc...

Cdlt.

c'est vrai que dans un script ça devient très douloureux, par exemple si tu as une contrainte CHECK(x!=y) + une contrainte y NOT NULL.

Dans ce cas, tu peux éventuellement renommer en block toutes les contraintes NOT NULL en bloc, en utilisant une nomenclature standard (ex: NN_ENAME pour ENAME NOT NULL).

Code:

1
2
3
4
5
 
create table t(x number, y number);
alter table t add constraint x_is_not_y check (x!=y);
alter table t modify x not null;
alter table t modify y not null;

ensuite, pour retrouver le nom de la contrainte, il faut du cran...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
select u.name owner,o.name table_name,c.name constraint_name,col.name column_name
from sys.user$ u,sys.obj$ o,sys.cdef$ cd,sys.ccol$ cc,sys.col$ col,sys.con$ c
where
o.owner# = u.user#
and cd.con#=c.con#
and cc.con#=c.con#
and o.obj#=cd.obj#
and cd.type#=7
and cc.obj# = col.obj#
and cc.intcol# = col.intcol#
and cc.obj#=o.obj#
and u.name='SCOTT'
/
 
OWNER  TABLE_NAME CONSTRAINT_NAME COLUMN_NAME
------ ---------- --------------- -----------
SCOTT  T          SYS_C005421     X
SCOTT  T          SYS_C005422     Y

finalement, tu fais un curseur du style

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
begin
for f in (
    select u.name owner,o.name table_name,c.name constraint_name,col.name column_name
    from sys.user$ u,sys.obj$ o,sys.cdef$ cd,sys.ccol$ cc,sys.col$ col,sys.con$ c
    where
    o.owner# = u.user#
    and cd.con#=c.con#
    and cc.con#=c.con#
    and o.obj#=cd.obj#
    and cd.type#=7
    and bitand(cd.defer,8)=8
    and cc.obj# = col.obj#
    and cc.intcol# = col.intcol#
    and cc.obj#=o.obj#
    and u.name='SCOTT'
) loop
execute immediate 'alter table "'||f.owner||'"."'||f.table_name||'" rename constraint "'||f.constraint_name||
'" to "NN_'||f.column_name||'"';
end loop;
end;
/

et tu vérifies :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
select u.name owner,o.name table_name,c.name constraint_name,col.name column_name
from sys.user$ u,sys.obj$ o,sys.cdef$ cd,sys.ccol$ cc,sys.col$ col,sys.con$ c
where
o.owner# = u.user#
and cd.con#=c.con#
and cc.con#=c.con#
and o.obj#=cd.obj#
and cd.type#=7
and cc.obj# = col.obj#
and cc.intcol# = col.intcol#
and cc.obj#=o.obj#
and u.name='SCOTT'
/
 
OWNER TABLE_NAME CONSTRAINT_NAME COLUMN_NAME
----- ---------- --------------- -----------
SCOTT T          NN_Y            Y
SCOTT T          NN_X            X

8-)

12/02/2007, 15h51
Ito

Citation:

Envoyé par laurentschneider

ensuite, pour retrouver le nom de la contrainte, il faut du cran...
...

Ouh la la, ça devient corsé :D
Bon, j'ai pris note de tout.

Once again, thanks...

Voir 40 message(s) de cette discussion en une page