Distinguer les CHECK CONSTRAINTS des NOT NULL

**Wurlitzer** · 27/09/2006, 14h54

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 :

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

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 :

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

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 :

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

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

**pifor** · 27/09/2006, 16h16

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

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

**Wurlitzer** · 27/09/2006, 16h46

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 :

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

**laurentschneider** · 27/09/2006, 17h03

les contraintes not null, c'est des contraintes CHECK.

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

Code :

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

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

**Wurlitzer** · 28/09/2006, 08h27

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

C'est a dire les CONSTRAINTS CHECK qui ne sont pas des NOT NULL

**LeoAnderson** · 28/09/2006, 09h14

not exist est donc votre ami !

**orafrance** · 28/09/2006, 10h06

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.

**McM** · 28/09/2006, 10h07

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

"COL IS" NOT NULL and COL > 10

**Wurlitzer** · 28/09/2006, 10h10

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

**Wurlitzer** · 28/09/2006, 10h18

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.

**orafrance** · 28/09/2006, 10h19

essaye ça :

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

**laurentschneider** · 28/09/2006, 10h25

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 :

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

**Wurlitzer** · 28/09/2006, 10h33

Envoyé par Fred_D

essaye ça :

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

**Wurlitzer** · 28/09/2006, 10h43

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 :

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

**laurentschneider** · 28/09/2006, 11h10

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

essaye

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

**Wurlitzer** · 28/09/2006, 11h45

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,

je ne suis jamais descendu aussi bas dans Oracle

Code :

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

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;

**laurentschneider** · 28/09/2006, 11h50

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

Distinguer les CHECK CONSTRAINTS des NOT NULL

Oracle

Discussions similaires

Partager

Partager