RE: Dropping FK constraints
Thanks Mario (and Jioi Lisicky) whose suggestions worked
and were very helpful in my program.
Alanoly.
-----Original Message-----
From: Mario R. Canto [mailto:mcanto@intec.unl.edu.ar]
Sent: Tuesday, May 18, 1999 10:45 AM
To: informix-list@iiug.org
Subject: Re: Dropping FK constraints
You can do:
========================================================================
dbaccess mydb - - <<!!!
{I have no manuals here but if you can do
unload to pipe 'dbaccess mydb - - '
or
output to pipe ... with no headers
or something like this, you don't need to use a tmp sql file}
unload to '/tmp/dropconstr.sql'select 'alter table mytable drop constraint' || constrname || ';'
from sysconstraints c, systables t
where t.tabname = 'mytable' and
t.tabid = c.tabid and
constrtype = 'R');
!!!
dbaccess mydatabase /tmp/dropconstr.sql
========================================================================
=
Hope this helps
Byte byte!
-- Mario R. Canto
mcanto@intec.unl.edu.ar
mcanto@intec.arcride.edu.ar
http://www.intec.unl.edu.ar/~mcanto
Alanoly Andrews escribis:
>
> Hi,
>
> Is there a way to delete all FK constraints on a table with a
> single SQL statement, without specifying the actual names of
> all those constraints? The Informix syntax seems to require
> contraint name(s) as mandatory input as in:
>
> alter table mytable drop constraint (const1, const2,.....)>
> What I would like to do is to have a statement of the type:
>
> alter table mytable drop constraint
> (select constrname from sysconstraints c, systables t
> where t.tabname = 'mytable' and t.tabid = c.tabid and
> constrtype = 'R');>
> Or even a statement which drops ALL constraints (FK or otherwise)
> on the table.
>
> Thanks.
>
> Alanoly J. Andrews.