Some opinions needed

Hi All Some opinions needed. Existing DB on SCO porting to SUN. Instead of saying: create table blabla ( d1 cha.... not null d2 cha.... not null primary key (d1) ) I redefine sql to create table to: create table blabla ( d1 ... d2 ... ) create unique index .... alter table ... add primary key... The reason that I thought was at a later stage if needed, it will be easy to specify on what DB space/where the index {primary key} must be created. But then when/if that is ever needed, the DBA can change it at that stage. What is the general standards that U are accustomed to ?. That is how to specify primary keys How to name indexes {unique, normal, descending etc..} EG table name = blabla PK index = pk_blabla index = i1_blabla unique idx = u1_blabla or what ever. Thanks for the first 20 replies -----Original Message----- From: Art S. Kagel [SMTP:kagel@bloomberg.net] Sent: Thursday, April 13, 2000 10:08 PM To: informix-list@iiug.org Subject: Re: A lazy sod writes... Obnoxio The Clown wrote: > Hi all, > > I have to find all the "unnamed" constraints in a database and rename them > using a human readable name. Has anybody done this and have a script to > share? Look into the source for the print_constraints() function in print_constraints.ec in the source to myschema. All generated implicit constraint names follow the pattern: <C>#_# where <C> is the lower case letter as below and '#'s represents some pair of numbers: character constraint type --------- ----------------------------------------- u UNIQUE or PRIMARY KEY r FOREIGN KEY n NOT NULL c CHECK In the ESQL/C code I use the following sscanf to detect an implicit PRIMARY KEY name: SELECT .... FROM sysconstraints.... WHERE constrtype = 'P' ...... ..... if (sscanf( sysconstraints.constrname, "u%d_%d", &a, &b ) == 2) { -- implicit name code -- } else { -- explicit name code -- } Try this SQL: SELECT * FROM sysconstraints WHERE constrname matches '[ncru][0-9]*_[0-9]*'; It MAY accidentally match some real constraint names but then I'd want to rename such oddities anyway. Art S. Kagel

Hannes Visagie wrote: > > Hi All > > Some opinions needed. > Existing DB on SCO porting to SUN. Instead of saying: > create table blabla > ( > d1 cha.... not null > d2 cha.... not null > primary key (d1) > )> > I redefine sql to create table to: > create table blabla > ( > d1 ... > d2 ... > )> > create unique index .... > alter table ... add primary key... That is the way I do it, if it were not obvious from the design of myschema! > The reason that I thought was at a later stage if needed, it will be easy > to specify on what DB space/where the index {primary key} must be created. > But then when/if that is ever needed, the DBA can change it at that stage. My reasons exactly, also it is far easier to reorg the table and indexes this way. > What is the general standards that U are accustomed to ?. > That is how to specify primary keys > How to name indexes {unique, normal, descending etc..} > EG table name = blabla > PK index = pk_blabla > index = i1_blabla > unique idx = u1_blabla > or what ever. I use something very similar. Tried naming indexes after the types of queries they support but that applied the same or similar name to many table's indexes and I grew tired of trying to find one more synonym for 'by name' or 'by primary key'. > Thanks for the first 20 replies Others need not apply?? -- Art S. Kagel & Family kagel@erols.com