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

Postgres: cannot reference two foreign keys from different tables #934

Open
mirakoukal opened this issue Aug 1, 2023 · 0 comments
Open

Comments

@mirakoukal
Copy link

When I try to configure multiple foreign keys in table definition, only one of them is created (first one in alphabetical order). If either of the two foreign keys is ommited, the other one is created properly. When bith are present only one gets created.
If table is created only with fk_st_customer_id FK and then definition both FKs is applied, the fk_st_customer_id gets dropped and the fk_st_config_id is created.

Example config:

apiVersion: schemas.schemahero.io/v1alpha4
kind: Table
metadata:
  name: sometable
  namespace: schemahero
spec:
  database: somedb
  name: sometable
  schema:
    postgres:
      primaryKey: [id]
      foreignKeys:
        - columns:
            - customer_id
          references:
            table: customers
            columns:
              - id
          onDelete: NO ACTION
          name: fk_st_customer_id
        - columns:
            - config_id
          references:
            table: configs
            columns:
              - id
          onDelete: NO ACTION
          name: fk_st_config_id
...

example output

\d+ sometable
...
Indexes:
    "sometable_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk_st_config_id" FOREIGN KEY (config_id) REFERENCES configs(id)
Access method: heap

Alternatively:

SELECT conrelid::regclass AS table_name,
       conname AS foreign_key,
       pg_get_constraintdef(oid)
FROM   pg_constraint
WHERE  contype = 'f'
AND    connamespace = 'public'::regnamespace
ORDER  BY conrelid::regclass::text, contype DESC;
 table_name  |    foreign_key    |                        pg_get_constraintdef
-------------+-------------------+---------------------------------------------------------------------
 sometable   | fk_st_config_id   | FOREIGN KEY (config_id) REFERENCES configs(id)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant