Re: relationship in a table

On Jan 25, 2007, at 11:46 AM, Kai Schlamp wrote:
> What is the best way to find out the relationship of two columns in a
> table?
> I want to know if it is 1:1, 1:N, or M:N. Is there an easy way to do
> this with a SQL statement?

If both tables have the same rowcount, and SELECT count(*) FROM a
JOIN b USING ... has the same count then it's 1:1 (if by 1:1 you mean
there must be a record for each value in both tables).

IF SELECT count(*) FROM b WHERE NOT EXISTS (SELECT * FROM a WHERE
a.field = b.field) is 0 and SELECT count(*)=count(DISTINCT field)
FROM a is true, then it's 1:N (a:b).

There might be some clever tests you can do with INTERSECT and the
like, too.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)