Urgent, renaming constraints
Hi all, when I am traing to create a new tables, with not null columns, in my database, I get an error teling me that a constraint name already exists. My problem was generated when I have exported and imported my database. I have not change the constraints name giving by informix in the exporte file(<database_name>.sql), so when i have imported the database the constraints was created with the name giving in the file <database_name>.sql. My question is , is there any solution to change my constraints name without droping and recreating the tables. Thanks for any advise ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com
Alter Table Drop Constraint, followed closely by Alter Table Add Constraint.
Alternatively, Alter Table Modify with a new constraint name might work. I
have not tried it.
--
Bashar Chalabi
CTL, London
samir BADAOUI <samir_badaoui@hotmail.com> wrote in message
news:7qh649$6ul$1@news.xmission.com...
>
> Hi all,
> when I am traing to create a new tables, with not null columns, in my
> database, I get an error teling me that a constraint name already exists.
> My problem was generated when I have exported and imported my database. I
> have not change the constraints name giving by informix in the exporte
> file(<database_name>.sql), so when i have imported the database the
> constraints was created with the name giving in the file
> <database_name>.sql.
> My question is , is there any solution to change my constraints name
without
> droping and recreating the tables.
>
> Thanks for any advise
>
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>
Yes, two solutions:
1) drop and recreate the constraints on the imported tables with ALTER
TABLE DDL commands
2) explicitely name the constraints on the new tables so they do not clash
with the imported constraints:
CREATE TABLE fred (
wilma integer NOT NULL CONSTRAINT nn_wilma_1...
);
Next time use myschema.ec to create the schema file for the dbimport! It
drops the pseudo constraint names so new ones will be created on the
target using the new tabids (one of the reasons I've maintained the beast
all these years). With the -l option myschema generates a script which
is compatible with the one generated by dbexport and can be used with
dbimport.
Art S. Kagel
samir BADAOUI wrote:
>
> Hi all,
> when I am traing to create a new tables, with not null columns, in my
> database, I get an error teling me that a constraint name already exists.
> My problem was generated when I have exported and imported my database. I
> have not change the constraints name giving by informix in the exporte
> file(<database_name>.sql), so when i have imported the database the
> constraints was created with the name giving in the file
> <database_name>.sql.
> My question is , is there any solution to change my constraints name without
> droping and recreating the tables.
>
> Thanks for any advise
>
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
I have had this exact same problem, and it was really scarey, beleive me.
What we ended up doing was modifying the schema file which was produced by
dbexport. By removing the explicit constraint names, the database engine
can assign new constraint names which are unique.
Thanks,
Kire
samir BADAOUI wrote in message <7qh649$6ul$1@news.xmission.com>...
>
>Hi all,
>when I am traing to create a new tables, with not null columns, in my
>database, I get an error teling me that a constraint name already exists.
>My problem was generated when I have exported and imported my database. I
>have not change the constraints name giving by informix in the exporte
>file(<database_name>.sql), so when i have imported the database the
>constraints was created with the name giving in the file
><database_name>.sql.
>My question is , is there any solution to change my constraints name
without
>droping and recreating the tables.
>
>Thanks for any advise
>
>______________________________________________________
>Get Your Private, Free Email at http://www.hotmail.com
I process the output of dbschema with...
sed -e '
s/ constraint .*\\.n[0-9]*_[0-9]*,$/,/
s/ constraint .*\\.n[0-9]*_[0-9]*$//
' <dbschema_output_file.sql >processed_file.sql
to remove the automatically generated constraint names.
In article <7qh649$6ul$1@news.xmission.com>, samir BADAOUI
<samir_badaoui@hotmail.com> writes
>
>Hi all,
>when I am traing to create a new tables, with not null columns, in my
>database, I get an error teling me that a constraint name already exists.
>My problem was generated when I have exported and imported my database. I
>have not change the constraints name giving by informix in the exporte
>file(<database_name>.sql), so when i have imported the database the
>constraints was created with the name giving in the file
><database_name>.sql.
>My question is , is there any solution to change my constraints name without
>droping and recreating the tables.
>
>Thanks for any advise
>
>______________________________________________________
>Get Your Private, Free Email at http://www.hotmail.com
Andrew Lennard andy@kontron.demon.co.uk