Обсуждение: [GENERAL] Why am I getting doubles?

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.

Igor Korot <ikorot01@gmail.com> writes:
> Is there a reason I'm seeing duplicate records on the query above?

Your example isn't complete, but I think the problem is your
WHERE clause isn't equating enough columns.  For instance,
if I do

db=# create table pp(f1 int, f2 int, primary key (f1,f2));
CREATE TABLE

that produces two rows in information_schema.key_column_usage:

 db                 | public            | pp_pkey         | db            | public       | pp         | f1          |
            1 |                               
 db                 | public            | pp_pkey         | db            | public       | pp         | f2          |
            2 |                         

Your WHERE clause can't tell the difference between these.

            regards, tom lane