Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Postgresql foreign key support #158

Closed
BadWord opened this issue Feb 24, 2018 · 11 comments
Closed

Postgresql foreign key support #158

BadWord opened this issue Feb 24, 2018 · 11 comments
Milestone

Comments

@BadWord
Copy link

@BadWord BadWord commented Feb 24, 2018

Steps to reproduce this issue

  1. Step 1; Open POSTGRESQL database in HeidiSQL
  2. Step 2; View a table, select tab foreign key

Current behavior

No foreign key can be assigned. Reason listed as engine does not support...

PostgreSQL does not have any engines that do not support foreign keys.

Expected behavior

A foreign key should be able to be assigned.

Possible solution

Postgresql format to assign foreign key:
city varchar(80) references cities(city)

also,
city varchar(80) references cities
works as the name matches.

Link: Postgresql tutorial for foreign key
https://www.postgresql.org/docs/8.3/static/tutorial-fk.html

Link: Stack overflow with a very good answer.
https://stackoverflow.com/questions/28558920/postgresql-foreign-key-syntax

Environment

Should affect the newest version.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 12, 2020

Latest release and builds at least allow to create foreign keys visually, but it fails with an SQL error when you click the "foreign columns" dropdown. Also, I guess this syntax is not PG friendly when creating the key:

ALTER TABLE "mytable"
	ADD CONSTRAINT "FK1" FOREIGN KEY (sourcecol) REFERENCES "myschema.mytable2" (targetcol);
@ansgarbecker ansgarbecker added this to the v10.4 milestone Jan 12, 2020
@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 13, 2020

Somone has a sample SQL query to retrieve foreign keys of a PG table?

@Buchheit-Jayson

This comment has been minimized.

Copy link

@Buchheit-Jayson Buchheit-Jayson commented Jan 27, 2020

I'm not too familiar but this SO post gives the following:

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

It seems to work well on my instance

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 28, 2020

Hehe, that is from the same post as rwilliams posted in a related issue.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 28, 2020

I added a foreign key using such a query:

ALTER TABLE "test"
    ADD CONSTRAINT "FK1" FOREIGN KEY
    ("Column 1", "Column 2") REFERENCES "anse2"."test" ("Column a", "Column b");

But the above query from SO did not list that key afterwards.
Instead, I have now this one, which lists it, but I have problems with grouping multi-columns:

SELECT
    refc.constraint_name,
    refc.update_rule,
    refc.delete_rule,
    kcu.table_name,
    kcu.column_name,
    ccu.table_schema,
    ccu.table_name,
    ccu.column_name,
    kcu.ordinal_position
FROM information_schema.referential_constraints refc,
    information_schema.key_column_usage AS kcu,
    information_schema.constraint_column_usage AS ccu
WHERE
   refc.constraint_schema='anse'
   AND refc.constraint_name = kcu.constraint_name
   AND refc.constraint_schema = kcu.table_schema
   AND ccu.constraint_name = refc.constraint_name
   AND kcu.table_name='test'
/*GROUP BY
    refc.constraint_name, refc.update_rule, refc.delete_rule,
    -- refc.constraint_catalog, refc.constraint_schema, refc.constraint_name,
    -- refc.unique_constraint_catalog, refc.unique_constraint_schema, refc.unique_constraint_name,
    -- refc.match_option,
    -- kcu.column_name
    kcu.table_name, kcu.column_name, kcu.ordinal_position */
ORDER BY kcu.ordinal_position;

Columns in the key are listed twice. Could you please help out here?

@ansgarbecker ansgarbecker changed the title Postgresql foreign key can not be assigned Postgresql foreign key support Jan 30, 2020
@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 30, 2020

Or should I probably parse the SQL code returned by pg_get_constraint_def:

SELECT conname,
  pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = 'anse.test'::REGCLASS
@zilzila

This comment has been minimized.

Copy link

@zilzila zilzila commented Feb 6, 2020

Columns in the key are listed twice. Could you please help out here?

SELECT
    refc.constraint_name,
    refc.update_rule,
    refc.delete_rule,
    kcu.table_name,
--     kcu.column_name,
    string_agg(distinct kcu.column_name, ',') as table_column_name, -- choose
--     array_agg(distinct kcu.column_name) as table_column_name,    -- appropriate
--     jsonb_agg(distinct kcu.column_name) as table_column_name,    -- one
    ccu.table_schema,
    ccu.table_name,
--     ccu.column_name,
    string_agg(distinct ccu.column_name, ',') as ref_column_name,
--     array_agg(distinct ccu.column_name) as ref_column_name,
--     jsonb_agg(distinct ccu.column_name) as ref_column_name,

--     kcu.ordinal_position
    string_agg(distinct kcu.ordinal_position::text, ',') as ord_position
--     array_agg(distinct kcu.ordinal_position) as ord_position
--     jsonb_agg(distinct kcu.ordinal_position) as ord_position
FROM
    information_schema.referential_constraints refc,
    information_schema.key_column_usage AS kcu,
    information_schema.constraint_column_usage AS ccu
WHERE
    refc.constraint_schema='anse'
    AND refc.constraint_name = kcu.constraint_name
    AND refc.constraint_schema = kcu.table_schema
    AND ccu.constraint_name = refc.constraint_name
    AND kcu.table_name='test'
GROUP BY
    refc.constraint_name,
    refc.update_rule,
    refc.delete_rule,
    kcu.table_name,
--     kcu.column_name,
    ccu.table_schema,
    ccu.table_name
--     ccu.column_name,
--     kcu.ordinal_position
ORDER BY
    -- kcu.ordinal_position;
    ord_position
;

PostgreSQL aggregate functions

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Feb 6, 2020

That query merges columns into one field. But better than my query, thanks!

@zilzila

This comment has been minimized.

Copy link

@zilzila zilzila commented Feb 7, 2020

Yes, I did it by analogy with MySql, where you can select several columns for a single constraint:
изображение

Here is a query without grouping by columns of the child table.

SELECT
    refc.constraint_name,
    refc.update_rule,
    refc.delete_rule,
    kcu.table_name,
    kcu.column_name as table_column_name,
--     string_agg(distinct kcu.column_name, ',') as table_column_name,
--     array_agg(distinct kcu.column_name) as table_column_name,
--     jsonb_agg(distinct kcu.column_name) as table_column_name,
    ccu.table_schema,
    ccu.table_name,
--     ccu.column_name,
    string_agg(distinct ccu.column_name, ',') as ref_column_name,
--     array_agg(distinct ccu.column_name) as ref_column_name,
--     jsonb_agg(distinct ccu.column_name) as ref_column_name,
    kcu.ordinal_position
--     string_agg(distinct kcu.ordinal_position::text, ',') as ord_position
--     array_agg(distinct kcu.ordinal_position) as ord_position
--     jsonb_agg(distinct kcu.ordinal_position) as ord_position
FROM 
    information_schema.referential_constraints refc,
    information_schema.key_column_usage AS kcu,
    information_schema.constraint_column_usage AS ccu
WHERE
    refc.constraint_schema='anse'
    AND refc.constraint_name = kcu.constraint_name
    AND refc.constraint_schema = kcu.table_schema
    AND ccu.constraint_name = refc.constraint_name
    AND kcu.table_name='test'
GROUP BY
    refc.constraint_name,
    refc.update_rule,
    refc.delete_rule,
    kcu.table_name,
    kcu.column_name,
    ccu.table_schema,
    ccu.table_name,
--     ccu.column_name,
    kcu.ordinal_position
ORDER BY
    kcu.ordinal_position
--     ord_position
;

I don't know what kind of result is exactly needed, in what form it is better to output the list. If you write how the rows\columns of the list should look, the grouping, then I can provide a suitable query.

Result of the first query:
изображение

Result of the second query (this comment):
изображение

P.S. My test configuration:

-- drop schema if exists prt cascade;
-- drop schema if exists cld cascade;

create schema prt;
create schema cld;

create table prt.parent_table (
    id      serial,
    parent1 integer,
    parent2 integer,
    parent3 integer,
    
    constraint PK_parent   primary key (id),
    constraint UK_parent1  unique (parent1),
    constraint UK_parent23 unique (parent2, parent3)
);

create table cld.child_table (
    id     serial,
    child1 integer,
    child2 integer,
    child3 integer,
    
    constraint PK_child primary key (id)
);

alter table cld.child_table 
    add constraint FK_child1  foreign key (child1)         references prt.parent_table (parent1),
    add constraint FK_child23 foreign key (child2, child3) references prt.parent_table (parent2, parent3)
;
@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Feb 10, 2020

Next build detects foreign keys, using @zilzila 's first query:

grafik

Just the foreign columns dropdown stays empty for some reason:

grafik
Will try to fix that as well.

Apart from that, the data tab now allows editing via dropdown in foreign key columns:

grafik

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Feb 11, 2020

Empty "Foreign columns" editor should now be fixed.

@ansgarbecker ansgarbecker unpinned this issue Feb 26, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
5 participants
You can’t perform that action at this time.