Number of Unique indexes allowed per table
A Sybase DBA new to Informix found a table with two unique indexes and asked how many unique indexes Informix permits, since Sybase allows only one. Answers: Informix imposes no such limit — you can create as many unique indexes (and corresponding unique constraints) on a table as you like, each enforced independently on insert/update; only the primary key is limited to one, and you can't have both a unique constraint and a primary key on the same key set. The lack of documentation is simply because there is no rule to state.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi Folks, I'm new to the group and new to the Informix world. By background is in Sybase. I came a cross a table today that has two unique indexes and I went searching for information on "The number of unique indexes allowed per table" because in Sybase it is 1. Can anyone point me to some good documentation and briefly explain how Informix allows more than 1?
"It wasn't designed by idiots" Sent from my iPhone > On 6 May 2015, at 15:25, JOHN HENRY <jhenry@gwkinvest.com> wrote: > > Hi Folks, > I'm new to the group and new to the Informix world. By background is in > Sybase. I came a cross a table today that has two unique indexes and I went > searching for information on "The number of unique indexes allowed per table" > because in Sybase it is 1. Can anyone point me to some good documentation and > briefly explain how Informix allows more than 1? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Spokey, Be nice. You can have as many unique indexes as you like. Online doc can be found here: http://www-01.ibm.com/support/docview.wss?uid=3Dswg27023505 cheers j. > On May 6, 2015, at 10:37 AM, Spokey Wheeler <spokey.wheeler@gmail.com> = wrote: >=20 > "It wasn't designed by idiots"=20 >=20 > Sent from my iPhone=20 >=20 >> On 6 May 2015, at 15:25, JOHN HENRY <jhenry@gwkinvest.com> wrote:=20 >>=20 >> Hi Folks,=20 >> I'm new to the group and new to the Informix world. By background is = in=20 >> Sybase. I came a cross a table today that has two unique indexes and = I went=20 >> searching for information on "The number of unique indexes allowed = per=20 > table"=20 >> because in Sybase it is 1. Can anyone point me to some good = documentation=20 > and=20 >> briefly explain how Informix allows more than 1?=20 >>=20 >>=20 >>=20 > = **************************************************************************= *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >>=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20
Hi John,
A table can have only one primary key, but is allowed to have multiple
unique indexes.
Not sure about documentation on this, but Informix will just manage and
enforce the uniqueness of each unique index when you insert/update a row.
i.e.
create table tab1 (
a integer,
b integer,c integer
);
create unique index tab1_uix1 on tab1 (a, b);
create unique index tab1_uix2 on tab2 (b, c);
insert into tab1 values (1, 1, 1); -- works
insert into tab1 values (1, 1, 2); -- fails because uniqueness of tab1_uix1is violated
insert into tab1 values (2, 1, 1); -- fails because uniqueness of tab1_uix2is violated
insert into tab2 values (1, 2, 3); -- works
Andrew
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of JOHN
HENRY
Sent: Wednesday, May 06, 2015 9:26 AM
To: ids@iiug.org
Subject: Number of Unique indexes allowed per table [35043]
Hi Folks,
I'm new to the group and new to the Informix world. By background is in
Sybase. I came a cross a table today that has two unique indexes and I went
searching for information on "The number of unique indexes allowed per
table"
because in Sybase it is 1. Can anyone point me to some good documentation
and briefly explain how Informix allows more than 1?
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Thank everyone for the quick responses. I follow what has been said. What had me questioning was really the fact that I couldn't find any documentation that stated the rules. Thanks again everyone.
That's just because there isn't a rule for this. Not only can you have multiple unique indexes, but you can have multiple unique constraints, one for each of those indexes - sets of keys (though you cannot have a unique constraint and a primary key constraint on the same key). Welcome to the community! You missed the annual IIUG Conference last week. Maybe we'll meet you at the conference next year. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 6, 2015 at 7:55 AM, JOHN HENRY <jhenry@gwkinvest.com> wrote: > Thank everyone for the quick responses. I follow what has been said. What > had > me questioning was really the fact that I couldn't find any documentation > that > stated the rules. Thanks again everyone. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113ea43e205a6e05156c00b3
Maybe John will present and take advantage of the free conference pass. "Case Study: Migrating from a Legacy Sybase System to Informix 12.1" -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Wednesday, May 06, 2015 11:08 AM To: ids@iiug.org Subject: Re: RE: Number of Unique indexes allowed per table [35050] That's just because there isn't a rule for this. Not only can you have multiple unique indexes, but you can have multiple unique constraints, one for each of those indexes - sets of keys (though you cannot have a unique constraint and a primary key constraint on the same key). Welcome to the community! You missed the annual IIUG Conference last week. Maybe we'll meet you at the conference next year. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 6, 2015 at 7:55 AM, JOHN HENRY <jhenry@gwkinvest.com> wrote: > Thank everyone for the quick responses. I follow what has been said. > What had me questioning was really the fact that I couldn't find any > documentation that stated the rules. Thanks again everyone. > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113ea43e205a6e05156c00b3 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
ZING! > On 6 May 2015, at 17:24, Andrew Ford <andrew@informix-dba.com> wrote: > > Maybe John will present and take advantage of the free conference pass. > > "Case Study: Migrating from a Legacy Sybase System to Informix 12.1" > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Wednesday, May 06, 2015 11:08 AM > To: ids@iiug.org > Subject: Re: RE: Number of Unique indexes allowed per table [35050] > > That's just because there isn't a rule for this. Not only can you have > multiple unique indexes, but you can have multiple unique constraints, one > for each of those indexes - sets of keys (though you cannot have a unique > constraint and a primary key constraint on the same key). > > Welcome to the community! You missed the annual IIUG Conference last week. > Maybe we'll meet you at the conference next year. > > Art > > Art S. Kagel, President and Principal Consultant ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do those > opinions reflect those of other individuals affiliated with any entity with > which I am affiliated nor those of the entities themselves. > > On Wed, May 6, 2015 at 7:55 AM, JOHN HENRY <jhenry@gwkinvest.com> wrote: > >> Thank everyone for the quick responses. I follow what has been said. >> What had me questioning was really the fact that I couldn't find any >> documentation that stated the rules. Thanks again everyone. >> >> >> >> > **************************************************************************** > *** >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --001a113ea43e205a6e05156c00b3 > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >