Hi everyone,
I'm working with the Retool database, and when I create a foreign key column pointing to the table I want to associate with, it doesn't allow me to select the ID as I was able to do with this table. Is this a bug?
My goal: To be able to create relationships through the ID.
Issue: It doesn't give me the option to select the ID of the table to relate.
Steps I've taken to troubleshoot:
Additional info: (Cloud or Self-hosted, screenshots)
The ccsa_client table has an auto-increment ID by default that comes with the table when it is created.
Hi @saul98, unfortunately this is a regression that the primary key ID isn't showing up in that dropdown, but it is being looked at.
Alternatively, here is some raw SQL that you could run in your app to set this up:
ALTER TABLE your_tablename
ADD COLUMN client_id INTEGER;
ALTER TABLE your_tablename
ADD CONSTRAINT fk_yourtable_client
FOREIGN KEY (client_id)
REFERENCES ccsa_cliente(id);
If you want ON DELETE or ON UPDATE cascade rules then you would set it up like this:
ALTER TABLE your_tablename
ADD CONSTRAINT fk_yourtable_client
FOREIGN KEY (client_id)
REFERENCES ccsa_cliente(id)
ON DELETE CASCADE
ON UPDATE CASCADE;
You would replace your_tablename with whatever your table is called, and fk_yourtable_client can be whatever you want to call this foreign key constraint rule. You can replace CASCADE with RESTRICT, SET NULL, or SET DEFAULT as well.
You can add a NOT NULL constraint on the foreign key like so:
ALTER TABLE your_tablename
ALTER COLUMN client_id SET NOT NULL;
Just make sure for that rule that any existing rows in the table have client_id values first.
@saul98 Ok I just wanted to update, we got this escalated and a fix to the Retool DB UI should be out on the next cloud release. I'll check back in once it's live!
. Thanks again 