Discussion :

[Ito]

Bonjour,

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

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

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

Merci d'avance.

[rouardg]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE liste modify (ipn not null) ;

[Ito]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[rouardg]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[Ito]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[laurentschneider]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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').

[Ito]

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.

[laurentschneider]

Lorsque tu fais

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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?

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

Sélectionner tout - Visualiser dans une fenêtre à part

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?

[pifor]

Ceci dit, on peut aussi penser que nommer les contraintes a aussi des avantages:http://asktom.oracle.com/pls/asktom/f?p=100:11:0:::11_QUESTION_ID:8906942258938#8960434782023

clarity,
meaning,
documentation,
readability in the data dictionary,
more meaningful information from error messages...

[laurentschneider]

Je ne suis pas d'accord avec "readability in the data dictionary", attendu que USER_TAB_COLUMNS.NULLABLE n'est pas mis à jour

[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.

[McM]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE liste MODIFY (ipn NOT NULL) ;

[Ito]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[laurentschneider]

tu peux toujours renommer la contrainte

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

[Ito]

tu peux toujours renommer la contrainte

Ok, sauf que je fais 1 étape supplémentaire (chercher la contrainte)...

[laurentschneider]

et ça te pose un problème ?

[Ito]

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.

[laurentschneider]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

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

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

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

[Ito]

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

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

Once again, thanks...