CodeIgniter Forums
Postgres and Foreign key data - Printable Version

+- CodeIgniter Forums (
+-- Forum: CodeIgniter 4 (
+--- Forum: CodeIgniter 4 Support (
+--- Thread: Postgres and Foreign key data (/showthread.php?tid=78201)

Postgres and Foreign key data - atsanna - 12-14-2020

I have a table on postgresql database in this structure:

-- table structure --
create table public.blocks
    id_quadrant         integer default 0 not null
        constraint quadrant_id_quadrant
            references public.quadrant
            on delete cascade,
    id_neighborhoods         integer default 0 not null
        constraint neighborhoods_id_neighborhoods
            references public.neighborhoods
            on delete cascade,
    id_aggr_block         integer default 0 not null
        constraint aggr_block_id_aggr_block
            references public.aggr_block
            on delete cascade,
    id_blocks serial not null
        constraint blocks_pk
            primary key,
    the_geom   geometry(MultiPolygon, 32638),
    layer       varchar(254),
    text        varchar(254),
    protection  varchar(50),
    attributes  varchar(254),
    zone        varchar(50),
    surveyed_1  varchar(50),
    area        double precision,
    density     double precision,
    data_house  varchar(3),
    created_at  timestamp,
    updated_at  timestamp,
    deleted_at  timestamp
-- comment --
comment on table public.blocks is 'Blocks';
-- owner --
alter table public.blocks
    owner to postgres;
-- index --
create unique index blocks_id_blocks_uindex
    on public.blocks (id_blocks);

I would like to know the foreign key data of the table

Using the getForeignKeyData () function invokes the _foreignKeyData function in the Connection.php file, which uses this query:

    tc.constraint_name, tc.table_name, kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='blocks';

As a result, I get 50 rows back, which is incorrect because there are only 3 foreign keys

The correct result would be obtained if the query were a SELECT DISTINCT.

In your opinion, is it more correct to modify the query with SELECT DISTINCT or to filter array of objects?

In what cases might the query without SELECT DISTINCT make sense?

Thanks for the replies