a question about sysindexes

According to the schema of sysindexes, a unique composite index is present on idxname + owner + tabid. Then why does the server disallow duplicate index name to be created even when the tables are different. create index test1 on table1(fld1); create index test1 on table2(fld1); The server does not allow the second index to be created with an error message "index test1 already exists". The unique index constraint should not be violated because the table on which the index is created is different in both the case. why?

Just a guess, are there other system catalog tables that track objects that may have unique object names? I am not sure which tables but look around for tables that have all objects. >The server does not allow the second index to be created with an >error message "index test1 already exists". > >The unique index constraint should not be violated because the table >on which the index is created is different in both the case. -- --------------------------------------------------------- Steven Hauser email: hause011@tc.umn.edu URL: http://www.sofbot.com/ ---------------------------------------------------------

triggerfish2001@hotmail.com wrote: > > According to the schema of sysindexes, a unique composite index > is present on idxname + owner + tabid. > > Then why does the server disallow duplicate index name to be created > even when the tables are different. > > create index test1 on table1(fld1); > create index test1 on table2(fld1);> > The server does not allow the second index to be created with an > error message "index test1 already exists". > > The unique index constraint should not be violated because the table > on which the index is created is different in both the case. > > why? Don't know. Probably because there is no "standard" syntax for "fully qualifying" an index. Unique names allows the syntax : DROP INDEX index-name; Brett Randall

triggerfish2001@hotmail.com wrote: > According to the schema of sysindexes, a unique composite index > is present on idxname + owner + tabid. > > Then why does the server disallow duplicate index name to be created > even when the tables are different. > > create index test1 on table1(fld1); > create index test1 on table2(fld1);> > The server does not allow the second index to be created with an > error message "index test1 already exists". > > The unique index constraint should not be violated because the table > on which the index is created is different in both the case. > > why? It depends in part on the mode of your database. If you'd been using a MODE ANSI database, then you could create two indexes with the same name but different owners. In a regular database, the index names are constrained to be unique across all the tables. I'm not clear why the index on sysindexes is a super-key (meaning that a subset of the columns in the index are the actual key), but I guess it allows an index-only operation given the index name to find the tabid, and the tabid is the key piece of data for finding out all about any given table. However, I equally don't know when you need the tabid given just the index name other than in a DROP INDEX statement, and IMNSHO those aren't sufficiently common for the index-only scan to be a significant benefit. In fact, I'd expect that the index on sysindexes would be different for MODE ANSI databases (where index name and owner must be unique) and all other databases (where index name irrespective of owner must be unique). But there are probably reasons why this is not desirable deep in the bowels of the optimizer. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

Jonathan Leffler wrote in message <3A661C50.3EEC3767@informix.com>... > >unique). But there are probably reasons why this is not desirable deep >in the bowels of the optimizer. > I reckon it'd just be historical choice, wouldn't it? Surely you can ask the old guys pushing around on their wheelchairs... Mr Fish, or may I call you Trigger? Perhaps you just need a naming convention. Here's my preferred one without any claim that it's ideal or better than any other: For table T 1) Primary index is called i0T 2) Other indexes are called i1T i2T etc etc etc You may choose other reasons to add "warts" to index names. Same applies to names of constraints, triggers, stored procedures associated intimately with a particular table, etc etc etc, and even to imply relationships between "tight clusters" of highly related tables.