checking constraints 
Author Message
 checking constraints

Please, could anyone give me a suggestion about checking the properties
of a costraint,
for example "on delete cascade", in a IDS database?

TIA,
Franco
--

Franco Fabbri
RASNA Imaging Systems
Via Panciatichi 26/3, 50127 Firenze Italia
Tel :   +(39) 0554369352
Fax :  +(39) 055413576



Sat, 05 Jun 2004 17:44:58 GMT
 checking constraints

Hi Franko,

try this sql:

-- FOREIGN KEY CONSTRAINTS: delrule=(R)estrict, (C)ascading
select
    c.constrname,
    r.delrule,
    t1.tabname Child,
    t2.tabname Parent
from
    sysreferences r,
    sysconstraints c,
    systables t1,
    systables t2,
    sysindexes i
where
    r.constrid = c.constrid and
    t2.tabid = r.ptabid and
    i.idxname = c.idxname and
    t1.tabid = i.tabid
order by
    t1.tabname,
    c.constrname;

-- FOREIGN KEY CONSTRAINTS (WITH UP TO FOUR COLUMNS):
select
    c1.constrname,
    r.delrule,
    t1.tabname Child,
    trim( trailing ',' from
nvl(trim(cl1.colname),'')||','||nvl(trim(cl2.colname),'')||','
||nvl(trim(cl3.colname),'')||','||nvl(trim(cl4.colname),'')) ChildCols,
    t2.tabname Parent,
    trim( trailing ',' from
nvl(trim(co1.colname),'')||','||nvl(trim(co2.colname),'')||','
||nvl(trim(co3.colname),'')||','||nvl(trim(co4.colname),'')) ParentCols
from
    sysconstraints c1,
    systables t1,
    sysindexes i1,
    sysconstraints c2,
    sysindexes i2,
    outer(syscolumns cl1), outer(syscolumns cl2),  outer(syscolumns cl3),
outer(syscolumns cl4),
    sysreferences r,
    systables t2,
    outer(syscolumns co1), outer(syscolumns co2), outer(syscolumns co3),
outer(syscolumns co4)
where
    i1.idxname = c1.idxname and
    t1.tabid = i1.tabid and
    cl1.tabid = i1.tabid and cl1.colno = i1.part1 and
    cl2.tabid = i1.tabid and cl2.colno = i1.part2 and
    cl3.tabid = i1.tabid and cl3.colno = i1.part3 and
    cl4.tabid = i1.tabid and cl4.colno = i1.part4 and
    r.constrid = c1.constrid and
    t2.tabid = r.ptabid and
    c2.constrid = r.primary and
    i2.idxname = c2.idxname and
    co1.tabid = i2.tabid and co1.colno = i2.part1 and
    co2.tabid = i2.tabid and co2.colno = i2.part2 and
    co3.tabid = i2.tabid and co3.colno = i2.part3 and
    co4.tabid = i2.tabid and co4.colno = i2.part4
order by
    t1.tabname,
    c1.constrname;

Regards
Mike



Please, could anyone give me a suggestion about checking the properties of a
costraint,
for example "on delete cascade", in a IDS database?
TIA,
Franco
--
Franco Fabbri
RASNA Imaging Systems
Via Panciatichi 26/3, 50127 Firenze Italia
Tel :   +(39) 0554369352
Fax :  +(39) 055413576



Sun, 06 Jun 2004 04:46:57 GMT
 checking constraints

Quote:

> Please, could anyone give me a suggestion about checking the properties
> of a costraint,
> for example "on delete cascade", in a IDS database?

Get the source to my dbschema replacement utility, myschema.  It
contains the file print_constraints.ec which will show you
everything you ever wanted to know about decoding the system
catelog tables related to constraints.  Myschema is part of the
package utils2_ak available for download from the IIUG Software
Repository.

As to the specific example of cascading deletes, if the column
sysreferences.delrule = 'C' for a particular constraint then it
was created with "ON DELETE CASCADE".

Art S. Kagel



Sun, 06 Jun 2004 08:21:17 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Column check constraint vs table check constraint

2. Change table check constraint to column check constraint?

3. Check Constraint vs. Foreign Key Constraint

4. check constraint or referential constraint

5. Check constraints over app based constraints

6. Check Constraint vs. Foreign Key Constraint

7. Check constraint w UDF on Update doesnt check

8. DELETE does not check CHECK constraint

9. Does anyone know why CHECK Constraint text does not appear

10. Union All view that is not updateable - problem with the check constraint

11. Check constraints

12. How to add the check-Constraints on table??


 
Powered by phpBB® Forum Software