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)