Discussion :

[Dajon]

Bonjour,

Je cherche à écrire une requête pour me permettre de vérifier l'existence d'une contrainte NOT NULL sur une colonne donnée.

Ma base:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Je voulais passer par les vues USER_CONSTRAINTS et USER_CONS_COLUMNS avec quelque chose du genre:

Table de test:

Code :

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

1
2
3
4
5
CREATE TABLE ADA_TEST1
(
  ID NUMBER NOT NULL,
  VAL VARCHAR2(15)
);

Requete:

Code :

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

1
2
3
4
5
6
7
SELECT COUNT(*)
FROM USER_CONS_COLUMNS CC
  INNER JOIN USER_CONSTRAINTS C ON (C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME)
WHERE C.TABLE_NAME='ADA_TEST1' 
AND CC.COLUMN_NAME='ID'
AND C.CONSTRAINT_TYPE='C'
AND C.SEARCH_CONDITION like '% NOT NULL';

Ce qui me renvoie une splendide:

ORA-00932: types de données incohérents ; attendu : NUMBER ; obtenu : LONG

En effet la colonne USER_CONSTRAINTS.SEARCH_CONDITION est un LONG...

Un tour sur ask tom, et je vois que:

1. Il ne faut pas utiliser de LONG (oui mais là je suis innocent)
2. On peut créer une fonction PL pour faire un SUBSTR()

Avant de créer la fonction PL qui ressemble plus à un palliatif qu'a autre chose, est-ce que quelqu'un verrait un autre moyen de tester l'existence d'une contrainte not null sur un colonne donnée?

Merci

[spdev666]

Bonjour,

Pourquoi ne pas utiliser ceci :

Code :

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

1
2
3
4
5
6
 
Select *
  From SYS.ALL_TAB_COLUMNS C
 Where C.TABLE_NAME = 'ADA_TEST1'
   And C.COLUMN_NAME = 'ID'
   And C.NULLABLE = 'Y'

[Rei Ichido]

Vous pouvez passer par la vue user_tab_columns :

Code :

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

1
2
3
4
 
SELECT *
  FROM user_tab_columns c
 WHERE c.nullable = 'N'

Edit : grillé

[Dajon]

mwarf, l'art de chercher midi à 14h

Merci

[mnitu]

C'est moitié de la solution

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
23
24
25
26
27
28
29
30
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> CREATE TABLE ADA_TEST1
  2  (
  3    ID NUMBER NOT NULL,
  4    VAL VARCHAR2(15) constraint chk_ada_test1_val check (val is not null)
  5  )
  6  /
 
Table created
SQL> SELECT c.TABLE_NAME, c.TABLE_NAME, c.NULLABLE
  2    FROM user_tab_columns c
  3   WHERE c.nullable = 'N'
  4     and c.TABLE_NAME = 'ADA_TEST1'
  5  /
 
TABLE_NAME                     TABLE_NAME                     NULLABLE
------------------------------ ------------------------------ --------
ADA_TEST1                      ADA_TEST1                      N
 
SQL> insert into ada_test1 values (1, Null);
 
insert into ada_test1 values (1, Null)
 
ORA-02290: violation de contraintes (MNI.CHK_ADA_TEST1_VAL) de vérification
 
SQL>

[Dajon]

C'est intéressant ça, je ne savais pas que le 'NOT NULL' après une colonne n'était pas strictement équivalent à une contrainte CHECK not null, au niveau du dictionnaire.

Mais du coup, c'est la piste esquissée dans ma requête initiale qui semble répondre aux deux cas.

Ce qui veux dire que je suis obligé de cast le LONG en VARCHAR avec une fonction en PL, en utilisant la méthode expliquée dans le lien de mon premier post...

C'est dommage, je ne trouve pas ça très "propre".

Merci mnitu en tout cas

[mnitu]

Lisez cet excellent article "working with long columns" et profitez de la coïncidence.

[Dajon]

En effet, belle coïncidence!

Merci pour l'article

Finalement, c'était moins trivial que ce que je pensais, et si je n'avais pas oublié l'existence de user_tab_columns.nullable, je n'aurais jamais vu que cela ne fonctionnait pas tout le temps.

Encore merci