Re: Split table syntax and take out all constraints...
Mahdi Sbeih wanted to create tables as RAW for loading, which fails when the schema has constraints, and asked how to split constraints into alter statements. Floyd Wellershaus offered a unix grep/awk script for primary keys; Mahdi noted unique constraints too. Jack Parker suggested querying sysconstraints, but Mahdi said it lacked column information. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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