[GENERAL] Why am I getting doubles?

Поиск
Список
Период
Сортировка
От Igor Korot
Тема [GENERAL] Why am I getting doubles?
Дата
Msg-id CA+FnnTzjQ4rrUnMPCDFhmZnLrQmYYtJ0Ctn1_Upxsi3fEigCUw@mail.gmail.com
обсуждение исходный текст
Ответы Re: [GENERAL] Why am I getting doubles?
Список pgsql-general
Hi,
The query below should get foreign keys for a specific table:

draft=# SELECT DISTINCT kcu.ordinal_position AS ordinal,
kcu.position_in_unique_constraint AS position, tc.constraint_name AS
name, tc.constraint_schema AS schema, tc.table_name AS table,
kcu.column_name AS column, ccu.table_name AS tableName,
ccu.column_name AS columnName, rc.update_rule, rc.delete_rule FROM
information_schema.table_constraints tc,
information_schema.key_column_usage kcu,
information_schema.constraint_column_usage ccu,
information_schema.referential_constraints rc WHERE tc.constraint_name
= kcu.constraint_name AND ccu.constraint_name = tc.constraint_name AND
rc.constraint_name = tc.constraint_name AND constraint_type = 'FOREIGN
KEY' AND tc.constraint_schema = 'public' AND tc.table_name =
'leaguescorehitter';
 ordinal | position |              name              | schema |
table       |  column  |    tablename    | columnname | update_rule |
delete_rule

---------+----------+--------------------------------+--------+-------------------+----------+-----------------+------------+-------------+-------------
       2 |        2 | leaguescorehitter_id_fkey1     | public |
leaguescorehitter | playerid | playersinleague | id         | NO
ACTION   | NO ACTION
       1 |        1 | leaguescorehitter_id_fkey1     | public |
leaguescorehitter | id       | playersinleague | id         | NO
ACTION   | NO ACTION
       1 |        1 | leaguescorehitter_id_fkey1     | public |
leaguescorehitter | id       | playersinleague | playerid   | NO
ACTION   | NO ACTION
       1 |        1 | leaguescorehitter_scoreid_fkey | public |
leaguescorehitter | scoreid  | scorehits       | scoreid    | NO
ACTION   | NO ACTION
       1 |        1 | leaguescorehitter_id_fkey      | public |
leaguescorehitter | id       | leagues         | id         | NO
ACTION   | NO ACTION
       2 |        2 | leaguescorehitter_id_fkey1     | public |
leaguescorehitter | playerid | playersinleague | playerid   | NO
ACTION   | NO ACTION
(6 rows)

draft=# \d leaguescorehitter
Table "public.leaguescorehitter"
  Column  |  Type   | Modifiers
----------+---------+-----------
 id       | integer |
 playerid | integer |
 scoreid  | integer |
 value    | numeric |
Indexes:
    "leaguescorehitter_playerid" btree (playerid)
Foreign-key constraints:
    "leaguescorehitter_id_fkey" FOREIGN KEY (id) REFERENCES leagues(id)
    "leaguescorehitter_id_fkey1" FOREIGN KEY (id, playerid) REFERENCES
playersinleague(id, playerid)
    "leaguescorehitter_scoreid_fkey" FOREIGN KEY (scoreid) REFERENCES
scorehits(scoreid)


If I don't have a foreing key with 2 fields everything works fine.

Is there a reason I'm seeing duplicate records on the query above?

Thank you.


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

Предыдущее
От: Alex Samad
Дата:
Сообщение: Re: [GENERAL] Question about paritioning
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [GENERAL] Why am I getting doubles?