Re: Split table syntax and take out all constraints...
You can do some unix script to it that will do the following:
1) cat myschema | grep -v "primary key (" > somefile
2) mv somefile myschema
###that will get rid of the primary key statement from your schema file.
Then operate on the primary key statements in the 'somefile' by doing
someting like:
for line in `cat somefile`
do
tab=`echo $line | awk '{print $5}' | awk -F\\_ '{print $2}'`
mykey=`echo $line | awk -F\\( '{print $2}'| sed 's/)//'
echo "alter table $tab add constraint primary key ( $mykey ) constraint
pk_${tab}" >> myschema
done
----- Original Message -----
From: "Mahdi Sbeih" <mahdi.sbeih@gmail.com>
Sent: Mon, July 14, 2008 4:24
Subject:Split table syntax and take out all constraints...
Hi All,
I have a dbschema output for a large database, and I want to create
all tables as RAW tables first in order to load data and then change
the type to STANDARD.
My problem is that I can't create a RAW table if the table syntax
contains constraints such as
create table mytable
(
my_key serial not null ,
my_name varchar(64) not null ,
desc1 varchar(32) not null ,
desc2 varchar(64) not null ,
primary key (my_key) constraint pk_mytable
);
Is there a way to split the above syntax into create and alters, so
the alters will be the constraints such as
create table mytable
(
my_key serial not null ,
my_name varchar(64) not null ,
desc1 varchar(32) not null ,
desc2 varchar(64) not null
);
alter table mytable add constraint primary key (my_key) constraintpk_mytable;
Any other ideas?
Thanks,
Mahdi
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
----- End of original message -----
Hi Floyd
What about other constraint type that aren't allowed in the RAW
tables, see below for example
create table tags
(
tag_flag smallint,
tag_name varchar(32),
unique (tag_flag) ,
unique (tag_name)
);
it seems with your solution, I have to cover all the cases which is a
little bit hard.
I thought there is away to use sysconstraints and other sys tables, to
retrieve all constraints , drop them and then re-create them later.
Are you aware of such solution?
-Mahdi
SELECT t.tabname, c.constrname
FROM systables t, sysconstraints c
WHERE t.tabid = c.tabid
AND constrtype in ('R','P') -- FK and PK, YMMV
Save them off and then recreate them. This can be expensive.
j.
Sane ego te vocavi. Forsitan capedictum tuum desit.
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]On Behalf Of Mahdi Sbeih
Sent: Monday, July 14, 2008 5:25 AM
To: informix-list@iiug.org
Subject: Re: Split table syntax and take out all constraints...
Hi Floyd
What about other constraint type that aren't allowed in the RAW
tables, see below for example
create table tags
(
tag_flag smallint,
tag_name varchar(32),
unique (tag_flag) ,
unique (tag_name)
);
it seems with your solution, I have to cover all the cases which is a
little bit hard.
I thought there is away to use sysconstraints and other sys tables, to
retrieve all constraints , drop them and then re-create them later.
Are you aware of such solution?
-Mahdi
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Thanks Jack, I got this before but it doesn't tell me what column(s) these constraints are on, basically it is not enough information to invoke an alter command. -Mahdi