can't delete!!

[joseph.l...@mindspring.com]

Hey y'all... having a problem with my Informix. Still being new to this, I'm
trying to figure this out on my own and I give up. Y'all have been so helpful
in the past, I thought I'd try you again.

I'm trying to delete anything from all of our tables that has a switch_id =
"2JB". Here's what I've come up with:

First I get a list of tables in the database that have "switch_id" as a
colname:

select unique tabname from systables a, syscolumns b where a.tabid =
b.tabid and b.colname = "switch_id"

And I put that in a for loop looking for switch_id = "2JB"

It ain't working, so I just have it display the table that has it in it, and
it shows one table, "switches".

I try:
delete from switches where switch_id = "2JB"

and I get:
692: Key value for constraint (informix.switches_key) is still being
referenced.

This switch_id is not in any other table in my database. Any ideas?

Thanks in advance for your generous help!!

Joseph

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

[Jay Hannah]

joseph.l...@mindspring.com wrote:
-snip!-

> It ain't working, so I just have it display the table that has it in it, and
> it shows one table, "switches".
>
> I try:
> delete from switches where switch_id = "2JB"
>
> and I get:
> 692: Key value for constraint (informix.switches_key) is still being
> referenced.
>
> This switch_id is not in any other table in my database. Any ideas?

Apparently you've set up referencial integrity to this table and (some
of) the records you are trying to delete are currently being referenced.

You can look in sysreferences or a dbschema output to find what tables
are referencing the "switches" table, and then handle the data in those
tables however you choose.

If you're sure you want to wipe out any and all data in the tables that
are referencing the "switches" table, you could alter the foreign key(s)
to enable cascade deletes, and then run your delete statement again.

$.02,

j

--
Jay Hannah
Quality Service Management, Inc.
[v:] 402/397-7077 x29 [f:] 402/397-7576 [p:] 402/899-1793

[June Tong]

joseph.l...@mindspring.com wrote:

> First I get a list of tables in the database that have "switch_id" as a
> colname:

...

> It ain't working, so I just have it display the table that has it in it, and
> it shows one table, "switches".
>
> I try:
> delete from switches where switch_id = "2JB"
>
> and I get:
> 692: Key value for constraint (informix.switches_key) is still being
> referenced.
>
> This switch_id is not in any other table in my database. Any ideas?

It isn't switch_id that it's complaining about; it's complaining about another
column - switeches_key. Apparently another table has a foreign key defined
against switches_key. Also note that the foreign key does not have to be called
"switches_key". It only has to be defined as referencing switches.switches_key.
You can use dbaccess table <switches> /info/constraints/reference/referenced to
identify which table is referencing switches_key. Or look in sysreferences &
syconstraints (the other way is easier).

June

[Rainer Schaub]

> There is a constraint to another table. In this other table

the field which is referenced has not the name switch_id, becausewith your select
at systables/indexes you can't find it.
make a dbschema -d DATABASE and look for the constraint
(informix.switches_key) and delete this one entry in the referenced table first.

good luck