Distinguer les CHECK CONSTRAINTS des NOT NULL

Version imprimable

Bonjour à tous,

Je cherche à écrire une requête qui puisse me donner la liste des CHECK CONSTRAINT d'une table

Je suis donc parti sur
Code:

1 2 3 4 5 6 SELECT constraint_name, search_condition FROM dba_constraints WHERE owner = 'MDSYS' AND table_name = 'MD$DIM' AND constraint_type ='C'
qui donne
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 CONSTRAINT_NAME SEARCH_CONDITION MD17 "CNAME" IS NOT NULL MD18A "DIM#" IS NOT NULL MD18B dim#>0 AND dim#<=32 MD19A "NAME" IS NOT NULL MD19B name = upper(name) AND length(name) <= 19 MD20 "LB" IS NOT NULL MD21 "UB" IS NOT NULL MD22A "SCALE" IS NOT NULL MD22B scale >= 0 MD23A "LEV" IS NOT NULL MD23B lev > 0
le problème est que je veux faire la distinction entre les CHECK et les NOT NULL et que cette information est dans la colonne SEARCH_CONDITION qui est un LONG.

Je ne peux donc pas ajouter de condition du genre
Code:

1 2 AND search_condition NOT LIKE '%IS NOT NULL'
Histoire de compliquer un peu les choses. Je suis en 8.1.7.4 et je dois pouvoir faire cette requête a partir d'un user DBA différent de SYS. Je ne peux donc pas faire un bout de code PL/SQL qui lit le LONG dans la table DBA_CONSTRAINTS puisque le droit d'exécution sur cette table provient du rôle DBA et je ne peux pas l'attribuer directement a mon utilisateur

Si vous avez des idées je suis preneur...

Réponse en anglais de Tom Kyte http://asktom.oracle.com/pls/ask/f?p...A:839298816582

Code:

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
ops$tkyte@ORA920LAP> create or replace function get_search_condition( 
p_cons_name in varchar2 ) return varchar2
  2  authid current_user
  3  is
  4      l_search_condition user_constraints.search_condition%type;
  5  begin
  6      select search_condition into l_search_condition
  7        from user_constraints
  8       where constraint_name = p_cons_name;
  9
 10      return l_search_condition;
 11  end;
 12  /
 
Function created.
 
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select constraint_name
  2    from user_constraints
  3   where get_search_condition(constraint_name) like '%NOT NULL%';
 
CONSTRAINT_NAME
------------------------------
SYS_C004792
SYS_C004794
SYS_C004181
....

Merci, mais cela ne me va pas tout a fait.

D'abord parce que je dois pouvoir faire mes recherches sur toutes les contraintes de tous les utilisateurs. C'est a dire utiliser la table DBA_CONSTRAINTS et non pas seulement USER_CONSTRAINTS. Or je peux accéder a cette table en SQL (droit par le rôle DBA) mais pas PL/SQL (pas de droit en direct et je ne peux pas en donner).

Ensuite parce que l'exécution de cette fonction très très lente mais bon la c'est peut être du a ma base de test qui n'est pas optimisé.

J'ai fini par trouver une solution qui même si elle n'est pas très jolie répond a mes "contraintes" :

En passant par une table intermédiaire et un champs LOB
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 CREATE TABLE CKC_CONSTRAINT ( owner VARCHAR2(30), object_name VARCHAR2(30), constraint_name VARCHAR2(30), last_ddl_time DATE, search_condition CLOB); INSERT INTO ckc_constraint SELECT ao.owner, ao.object_name, dc.constraint_name, ao.last_ddl_time, TO_LOB (dc.search_condition) FROM all_objects ao, dba_constraints dc WHERE dc.constraint_type = 'C' AND ao.owner IN ('SCH1', 'SCH2') AND dc.table_name = ao.object_name AND dc.owner = ao.owner; SELECT owner, object_name, constraint_name,last_ddl_time, search_condition FROM dx_ckc_constraint where dbms_lob.instr(search_condition, 'IS NOT NULL') = 0 ORDER BY last_ddl_time DESC, owner, object_name DROP TABLE DX_CKC_CONSTRAINT;

27/09/2006, 17h03
laurentschneider
les contraintes not null, c'est des contraintes CHECK.

Pour savoir quelles colonnes ne peuvent pas être nul, emploie simplement
Code:

1 2 select table_name,column_name from user_tab_columns where nullable='N';
Tu obtiendras toutes les colonnes CLE PRIMAIRE et CHECK IS NOT NULL

A+
Laurent
28/09/2006, 08h27
Wurlitzer

Merci, mais je veux juste l'inverse....

C'est a dire les CONSTRAINTS CHECK qui ne sont pas des NOT NULL
28/09/2006, 09h14
LeoAnderson

not exist est donc votre ami ! :)
28/09/2006, 10h06
orafrance

non, c'est plus compliqué. Il faut compter le nombre de check contraint sur la table et le nombre de colonne NOT NULL, si c'est égale alors il n'y a pas de check autre que NOT NULL.
28/09/2006, 10h07
McM

Mais tu peux avoir une contrainte "col in (1,2)" sur une colonne Nullable=NO
Donc chercher par le NULLABLE, je pense pas.

Chercher aussi par le search_condition...
Tu peux aussi avoir

Citation:

"COL IS" NOT NULL and COL > 10
28/09/2006, 10h10
Wurlitzer

Citation:

Envoyé par LeoAnderson

non, c'est plus compliqué. Il faut compter le nombre de check contraint sur la table et le nombre de colonne NOT NULL, si c'est égale alors il n'y a pas de check autre que NOT NULL.

Oui mais, c'est un ami peu fiable. Puisque je peux avoir une contrainte NOT NULL sur une colonne et en plus contrainte CHECK personnalisée sur cette même colonne. Dans ce cas, le NOT EXIST va me faire "disparaître" ma contrainte CHECK.

J'ai bien avoir confiance dans mes amis ;)
28/09/2006, 10h18
Wurlitzer

Citation:

Envoyé par McM

"COL IS" NOT NULL and COL > 10

J'avais pas pensé au cas ou le mot clé "NOT NULL" était inclus dans une contrainte personnalisé. Du coup je n'ai meme plus confiance en moi ;)

Ca devient une vrai usine a gaz ce truc ! ! Pour finalement, quelque chose de pas si important que cela.

essaye ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select check_cons.owner, check_cons.table_name, constraint_name,search_condition,nb_check,nb_nullable
  from (
	   SELECT ao.owner, ao.table_name, dc.constraint_name, 
	          dc.search_condition, COUNT(dc.constraint_type) OVER (PARTITION BY dc.table_name, dc.owner) nb_check
	     FROM dba_tables ao, dba_constraints dc
	    WHERE dc.constraint_type = 'C'
	      AND ao.owner IN ('SCH1', 'SCH2')
	      AND dc.table_name = ao.table_name
	      AND dc.owner = ao.owner
		 ) check_cons, 
		 (
		  SELECT owner, table_name, count(1) nb_nullable
		    FROM dba_tab_columns
		   WHERE nullable = 'N'
  		     AND owner IN ('SCH1', 'SCH2')
		  GROUP BY owner, table_name
		 ) cons_null
  where  cons_null.table_name = check_cons.table_name
    and  cons_null.owner = check_cons.owner
	and nb_check > nb_nullable

Citation:

Envoyé par McM

Mais tu peux avoir une contrainte "col in (1,2)" sur une colonne Nullable=NO
Donc chercher par le NULLABLE, je pense pas.

ouais, à voir...

qu'en est-il si l'utilisateur crée une contrainte CHECK avec comme condition IS NOT NULL?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
SQL> create table t(x number not null, y number check ("Y" IS NOT NULL), constraint c check (x=y));
 
Table created.
 
SQL> desc t
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 X                             NOT NULL NUMBER
 Y                                      NUMBER
 
SQL> select CONSTRAINT_NAME,SEARCH_CONDITION from user_constraints where table_name='T';
CONSTRAINT_NAME  SEARCH_CONDITION
---------------- ----------------
SYS_C002971      "X" IS NOT NULL
SYS_C002972      "Y" IS NOT NULL
C                x=y

la seule façon de savoir si la colonne a été définie comme NOT NULL, c'est de regarder dans USER_TAB_COLUMNS, non?

Citation:

Envoyé par Fred_D

essaye ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select check_cons.owner, check_cons.table_name, constraint_name,search_condition,nb_check,nb_nullable
  from (
	   SELECT ao.owner, ao.table_name, dc.constraint_name, 
	          dc.search_condition, COUNT(dc.constraint_type) OVER (PARTITION BY dc.table_name, dc.owner) nb_check
	     FROM dba_tables ao, dba_constraints dc
	    WHERE dc.constraint_type = 'C'
	      AND ao.owner IN ('SCH1', 'SCH2')
	      AND dc.table_name = ao.table_name
	      AND dc.owner = ao.owner
		 ) check_cons, 
		 (
		  SELECT owner, table_name, count(1) nb_nullable
		    FROM dba_tab_columns
		   WHERE nullable = 'N'
  		     AND owner IN ('SCH1', 'SCH2')
		  GROUP BY owner, table_name
		 ) cons_null
  where  cons_null.table_name = check_cons.table_name
    and  cons_null.owner = check_cons.owner
	and nb_check > nb_nullable

Ca renvoie toute les contraintes (CHECK et NOT NULL) des tables qui on au moins une contraintes CHECK. Donc c'est pas tout a fait ce que je veux. Mais l'idée est bonne, je vais essayer de la transposer au niveau colonne avec DBA_CONS_COLUMNS

Bon j'ai modifié comme ci dessous et je ne suis pas bien avancé puisque cela rameme toute les contraintes (CHECK et NOT NULL) pour une colonne qui à au moins une contrainte CHECK mais je n'arrive pas a éliminer la contrainte NOT NULL pour n'afficher que la contrainte CHECK.

Bon ce coup ci j'abandonne....

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
select check_cons.owner, check_cons.table_name, constraint_name,search_condition,nb_check,nb_nullable
  from (
	   SELECT ao.owner, ao.table_name, dc.constraint_name, 
	          dc.search_condition, COUNT(dc.constraint_type) OVER (PARTITION BY dcc.column_name, dc.table_name, dc.owner) nb_check
	     FROM dba_tables ao, dba_constraints dc, dba_cons_columns dcc
	    WHERE dc.constraint_type = 'C'
	      AND ao.owner IN ('PENSOINS', 'SCH2')
	      AND dc.table_name = ao.table_name
	      AND dc.owner = ao.owner
		  AND dcc.owner = dc.owner
		  AND dcc.constraint_name = dc.constraint_name
		 ) check_cons, 
		 (
		  SELECT owner, table_name, column_name, count(1) nb_nullable
		    FROM dba_tab_columns
		   WHERE nullable = 'N'
  		     AND owner IN ('PENSOINS', 'SCH2')
		  GROUP BY owner, table_name, column_name
		 ) cons_null
  where  cons_null.table_name = check_cons.table_name
    and  cons_null.owner = check_cons.owner
	and nb_check > nb_nullable

on peut y arriver avec les structures internes d'Oracle, car Oracle sait comment relier une colonne NOT-NULLABLE à sa contraint CHECK !

essaye

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
SQL> select OWNER,TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION from dba_constraints where owner='SCOTT';
OWNER           TABLE_NAME       CONSTRAINT_NAME  SEARCH_CONDITION
--------------- ---------------- ---------------- ----------------
SCOTT           DEPT             PK_DEPT
SCOTT           EMP              PK_EMP
SCOTT           EMP              FK_DEPTNO
SCOTT           T                SYS_C002980      x>1
SCOTT           T                SYS_C002979      "Y" IS NOT NULL
 
SQL> select OWNER,TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION 
from dba_constraints 
where owner='SCOTT' and (owner,table_name,constraint_name) not in (
  select u.name,o.name,c.name
  from user$ u,obj$ o, con$ c, cdef$ cd
  where o.owner# = u.user# and cd.con#=c.con# and o.obj#=cd.obj# and cd.type#=7);
OWNER           TABLE_NAME       CONSTRAINT_NAME  SEARCH_CONDITION
--------------- ---------------- ---------------- ----------------
SCOTT           T                SYS_C002980      x>1
SCOTT           EMP              FK_DEPTNO
SCOTT           DEPT             PK_DEPT
SCOTT           EMP              PK_EMP

Bravo ! ! ! C'est exactement ce qu'il me fallait.

J'ai juste modifié un peu le code pcq je ne suis pas connecté avec SYS et à cause de problème de perf avec le NOT IN.

Mais là je dis respect, :ave: je ne suis jamais descendu aussi bas dans Oracle
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT owner, table_name, constraint_name FROM dba_constraints WHERE owner = 'SCOTT' AND constraint_type = 'C' MINUS SELECT u.NAME, o.NAME, c.NAME FROM SYS.user$ u, SYS.obj$ o, SYS.con$ c, SYS.cdef$ cd WHERE o.owner# = u.user# AND cd.con# = c.con# AND o.obj# = cd.obj# AND cd.type# = 7;

28/09/2006, 11h50
laurentschneider

oui, mais attention, les structure internes ne sont pas documentées, donc il faut bien tester avec toutes les versions que tu employes (9iR2,10g,10gR2, etc), car rien ne garantit que ce code marche dans en Oracle 5 ni en Oracle 15...