Re: Finding Composite Keys in Informix?

Count can only work on one column at a time. If you are doing this sort of task, check out analyse_idx at www.iiug.org. This is a 4gl program which does all of this for you except a little different. It looks at your system catalogues and optionally your source code and reports a score for each column in each table (or for a table you select) rating how it would perform as an index. Things it looks at: Number of unique values per column number of occurences of this column/table in your where clauses Possible joins of this column to other columns (same name and type) in other tables Existing index information. As its documentation indicates it is a crowbar not a scalpel, gives you a starting point for further analysis, not a script you can execute to create indices. cheers j. At 07:14 PM 10/27/97 -0600, you wrote: }I have an undocumented database I am trying to reverse-engineer. In }particular I want to find potential candidate keys. Typically I use the }following syntax (for keys consisting of a single column): } }If select count(*) from t; = select count(distinct c) from t; then c is }a potential candidate key for table t. } }To search for potential composite candidate keys I try: } } select count(distinct c1, c2) from t; } }This works with a number of other DBMS where the semantics of the query }is to count the number of distinct (c1,c2) pairs. Informix, however, }gives me a syntax error. When I omit the count aggregate, however, it }works as expected. } } select distinct c1, c2 from t; } } }Is there something wrong with the way I am trying to find potential }composite candidate keys? Is there another way to do this in SQL? } }Thanks for any help, }-- }Robert Winkler }U.S. Army Research Laboratory }Beckman Institute, Room 4357 }405 North Matthews Ave, Urbana, IL 61801 } }