Re: relationship in a table

Поиск
Список
Период
Сортировка
От Jim Nasby
Тема Re: relationship in a table
Дата
Msg-id 6435F892-1877-4EEC-ACB0-54E00C6A14C2@decibel.org
обсуждение исходный текст
Ответ на relationship in a table  ("Kai Schlamp" <stroncococcus@gmx.de>)
Список pgsql-general
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)



В списке pgsql-general по дате отправления:

Предыдущее
От: Jim Nasby
Дата:
Сообщение: Re: Index bloat of 4x
Следующее
От: Jim Nasby
Дата:
Сообщение: Re: VACUUM ANALYZE taking a long time, %I/O and %CPU very low