+17
Accepted

FK with serial data type on PostgreSQL

opena 3 years ago updated by hannah gryska 1 day ago 9

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

 

+1

Was this ever implemented? FKs are still being inherited as serial data type fields on child tables for me in PostgreSQL.

Can you provide update? I am also getting issue with self-referencing FK to table with Serial types...forcing NULL or NOT NULL messes up the definition and causes an error

+1

Hello John,

Thank you for bringing this to our attention. Though this was fixed a few years ago, a recent release reintroduced this bug again recently. It is a known bug and the team is currently working to issue the fix in the next release. We are hoping by early next week.

We will keep you updated on the progress of this bug fix. Thank you again and sorry for the inconvenience.