This may be a simple newbie question, but..... (about Indexes)

I'm working with Informix Dynamic Server Version 7.30.UC8 on an AIX machine. I have a table with about 3 fields that I want to create a Unique index on using sql. If I try to insert or update a record so that the three fields match the three fields on any other record, refuse to accept it as an entry. I know that I can make individual fields unique but how do I set 3 as a group? I've been looking at cluster indexes, but I'm not sure if this is what I would need to use or understand much about cluster indexes. Help? Tony!

CREATE UNIQUE INDEX i1_table1 ON table1 (col1,col2,col3); Hal Maner M Systems International, Inc. www.msystemsintl.com Tony! <NoSpam@Thank.You.com> wrote in message news:LoamOcq2qyZdjQuNbKNmaXq4NUh6@4ax.com... > I'm working with Informix Dynamic Server Version 7.30.UC8 on an AIX > machine. > > I have a table with about 3 fields that I want to create a Unique > index on using sql. > > If I try to insert or update a record so that the three fields match > the three fields on any other record, refuse to accept it as an entry. > > I know that I can make individual fields unique but how do I set 3 as > a group? > > I've been looking at cluster indexes, but I'm not sure if this is what > I would need to use or understand much about cluster indexes. > > Help? > > > Tony! >

create unique index <index name> on <table>.(<col1>,<col2>,<col3>) "Tony!" <NoSpam@Thank.You.com> wrote in message news:LoamOcq2qyZdjQuNbKNmaXq4NUh6@4ax.com... > I'm working with Informix Dynamic Server Version 7.30.UC8 on an AIX > machine. > > I have a table with about 3 fields that I want to create a Unique > index on using sql. > > If I try to insert or update a record so that the three fields match > the three fields on any other record, refuse to accept it as an entry. > > I know that I can make individual fields unique but how do I set 3 as > a group? > > I've been looking at cluster indexes, but I'm not sure if this is what > I would need to use or understand much about cluster indexes. > > Help? > > > Tony! >

You cannot create a multiple column index using the dbaccess menus. Go into the QUERY>NEW and enter the CREATE INDEX command in SQL manually then RUN it: create unique index u1 on tab1 (col1, col2, col3); In Informix CLUSTERed indexes sort the data into the same physical order as the index which speeds sequential access by that key ONLY. They are not maintained over time and have to be reorganized periodically with: ALTER INDEX i3 TO NOT CLUSTER; ALTER INDEX i3 TO CLUSTER; Art S. Kagel "Tony!" wrote: > > I'm working with Informix Dynamic Server Version 7.30.UC8 on an AIX > machine. > > I have a table with about 3 fields that I want to create a Unique > index on using sql. > > If I try to insert or update a record so that the three fields match > the three fields on any other record, refuse to accept it as an entry. > > I know that I can make individual fields unique but how do I set 3 as > a group? > > I've been looking at cluster indexes, but I'm not sure if this is what > I would need to use or understand much about cluster indexes. > > Help? > > Tony!

Thanks to all for the replies.. :) -T- On Fri, 25 Aug 2000 14:36:00 -0400, "Art S. Kagel" <kagel@bloomberg.net> wrote: >You cannot create a multiple column index using the dbaccess menus. Go >into the QUERY>NEW and enter the CREATE INDEX command in SQL manually then >RUN it: > >create unique index u1 on tab1 (col1, col2, col3);> >In Informix CLUSTERed indexes sort the data into the same physical order as >the index which speeds sequential access by that key ONLY. They are not >maintained over time and have to be reorganized periodically with: >ALTER INDEX i3 TO NOT CLUSTER; >ALTER INDEX i3 TO CLUSTER;> >Art S. Kagel > >"Tony!" wrote: >> >> I'm working with Informix Dynamic Server Version 7.30.UC8 on an AIX >> machine. >> >> I have a table with about 3 fields that I want to create a Unique >> index on using sql. >> >> If I try to insert or update a record so that the three fields match >> the three fields on any other record, refuse to accept it as an entry. >> >> I know that I can make individual fields unique but how do I set 3 as >> a group? >> >> I've been looking at cluster indexes, but I'm not sure if this is what >> I would need to use or understand much about cluster indexes. >> >> Help? >> >> Tony!