- SqlDBM Forum
-
Bugs
+17
Fixed
FK with serial data type on PostgreSQL
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!
Customer support service by UserEcho
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.
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
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
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.
Hi John,
Thanks for your patience. This bug has been fixed and you should no longer experience this issue. Please let me know if you have any questions. Thanks again for bringing this to our attention.
I had this issue in PG and did a work around with a post script (if anyone wants it). Also @hannah. The same thing happens in Snowflake when you assign a sequence to a PK, it gets inherited down to the FK. Will that be fixed as well?
Hey Neil,
This was a bug but it was unfortunately reintroduced recently. We are working on fixing this and I'll keep both you and this thread updated on this front. Thank you!