+10
Accepted

FK with serial data type on PostgreSQL

opena 10 months ago updated by Support 3 months ago 6

Hi there!

FKs are being inherited as serial data type fields on child tables. For example, table CATEGORIES has category_id as a serial PK, then, a table PRODUCTS is related to CATEGORIES (regardless of the relationship is either identifying or not). In the table PRODUCTS, the field category_id is added as a serial data type which shouldn't be right.

That inheritance should be changed from serial to int, same to smallint instead of smallserial and bigint instead of bigserial. If not, the DBMS will create sequences for the PK and the FK too.

Maybe I'm doing something wrong, in which case, I appreciate you can tell me what is.

Thanks in advance!

By the way, congrats for so awesome work!

Under review

Dear user,

We are looking into this issue. We will notify you as soon as we know more details.

Team - SqlDBM

Has there been any movement on this? Thanks!

It has not been done yet, but it is in our development pipeline, once it gets prioritized, team will work on it.

Thanks,

Team - SqlDBM

This is also broken on MySQL.

When primary key is "serial" data type and then added as a foreign key to another table, it should automatically be set as unsigned bigint on the child table. The child table will have two columns with autoincrement (PK and FK) which is not allowed.


Is there a timeline for a fix on this?

Sadly this is our problem too and is an absolute show stopper. It is very common to model an entity using a surrogate key, or using a SERIAL value for keys like user_id, or account_id. Hopefully this gets fixed soon and we will revisit using this solution then. My guess is that if it is not being upvoted, perhaps you are losing people during the evaluation because it is so broken. Anyway - good luck. Would love to see this solution become viable.

+1
Accepted

Dear Users,

Thank your for your patience.

We have worked on this issue and it is in testing phase right now, hopefully will be released in next 7 days. Here is quick sneak peak of the fix. 


Hope this helps.

Thanks,

Team - SqlDBM