Re: Finding Composite Keys in Informix?
Jack Parker wrote:
> 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?
There are at least three ways to do anything in SQL (Kagel's Law)! Try:
select distinct c1, c2 from t into temp p;
select count(*) from p;
drop table p;
-OR in 4GL or ESQL/C just-
select distinct c1,c2 from t into temp p;Then check the value of sqlca.sqlcode[2] (or sqlca.sqlcode[3] for 4GL)
which contains the number of rows processed into the temp table before
dropping the temp table.
Art S. Kagel