DDL generation with foreign key in different schemata

Hello.

My setup: Postgresql (9.4)

On my database, I have various schemata, beside 'public', for example 'reference'. Whenever, I want to generate DDL of a table in 'public' (for example here, 'adressebuch'), I got:

-- auto-generated definition
create table adressebuch
(
adresse_id serial not null
constraint adressebuch_pkey
primary key,
adresse_typ smallint
constraint adressebuch_adresse_typ_fkey
references tbl_adresse_typ (id)
)
;

and have to correct the reference from 'tbl_adresse_typ' to 'reference.tbl_adresse_typ', because the referenced table is in another schema. The code is now then usable to deploy to another server.

 

My question is, whether there is an option to have this correct generated, or could it be, that I missed some settings? 

 

PS: For testing, you can use Flyway and the sql files at my repo: https://github.com/gipfeli/KProject/tree/master/SQL_Flyway 

 

Thank you and have a nice day.

6 comments
Comment actions Permalink

I don't know if this should be its own thread, but there's another error when using DDL generation after altering a table, where it doesn't recognize the new table properties. For example if I alter a table like this

    `ALTER TABLE dbname.tablename MODIFY COLUMN columnName VARCHAR(20) AFTER otherColumn`

and then go on to generate the DDL, it'll give me something with the old column order, before making the change.

I thought maybe the issue is related? If you recently moved your reference table to another schema, perhaps there's a cached version of your DB somewhere that generate DDL didn't pick up on?

Anyways, I've tried restarting, closing all tabs etc to see if it would clear things up to no avail.

0
Comment actions Permalink

Are you using Postgresql? I don't think you can  reorder the order of the columns without, either delete the whole table and make it new or create new columns and move data into it.

Read more here, https://wiki.postgresql.org/wiki/Alter_column_position 

Btw, I've never moved my table around :)

0
Comment actions Permalink

I'm using MySQL! And it works on the backend, I can see the columns re-ordered in phpmysql.

0
Comment actions Permalink

Hi,

Could you specify DataGrip version you use?

As for changing column order,  you need sometimes synhronize manually to see changes.

As for incorrect FK reference to another schema, it's a bug for sure.

Thank you.

0
Comment actions Permalink

Hi Vasily

I'm using version 2017.1.1. Thank you and have a nice weekend.

 

 

0
Comment actions Permalink

Hi,

Follow the issue https://youtrack.jetbrains.com/issue/DBE-3526 and related issues.

This issue is under development now. 

Thank you.

1

Please sign in to leave a comment.