supabase
Dashboard UI: can't set a row item to null if the column has foreign key relationship (column is nullable)
Bug report
Dashboard UI: can't set a row item to null if the column has foreign key relationship (column is nullable)
To reproduce:
Creat a table where a column (parent_id) has a foreign key relationship to its own id field. Also make both id and parent_id use text type. Now create two rows A and B, and have B's parent_id link to A's id. Now try to set parent_id of row B to NULL.
- [ ] I confirm this is a bug with Supabase, not with my own application.
- [ ] I confirm I have searched the Docs, GitHub Discussions, and Discord.
Describe the bug
A clear and concise description of what the bug is.
To Reproduce
Steps to reproduce the behavior, please provide code snippets or a repository:
- Go to '…'
- Click on '…'
- Scroll down to '…'
- See error
Expected behavior
A clear and concise description of what you expected to happen.
Screenshots
If applicable, add screenshots to help explain your problem.
System information
- OS: [e.g. macOS, Windows]
- Browser (if applies) [e.g. chrome, safari]
- Version of supabase-js: [e.g. 6.0.2]
- Version of Node.js: [e.g. 10.10.0]
Additional context
Add any other context about the problem here.
Hello @ericxl, we were not able to reproduce the bug. Can you take a look at the steps that we took and see if there were anything that is different from how you encountered the bug.
The steps we took:
- First, we cloned the repo and built the repo within a codespace environment(GitHub codespaces)
- Next, we created a new Supabase project and added the following SQL command to generate a table:
And next added in “A” and “B” data rows into the table with:
This produced:
Next, we tried to set the parent_id of row “B” to equal to NULL and there was no error using the SQL command:
UPDATE items SET parent_id = NULL WHERE id = 'B';
This produced:
To my understanding, there should have been an error with setting the parent_id of row B with NULL Please let me know if any differences to approaching the error. Thanks.
Versions: supabase-js: 2.41.1 node.js : v20.14.0 Linux OS Chrome Browser
Hello, I think we figured out the issue that you were having. After following your steps, we were able to create a table with two rows. We then tried to update the value of the parent_id which had the foreign key relationship to null through the UI and we got and error popup which said: Failed to update table row: insert or update on table "my_table" violates foreign key constraint "my_table_parent_id_fkey". Is the same issue that you had?
Hey @henryis300
I think I'm having a similar problem as @ericxl
If I have this column devices.leader_id that is Nullable and has a foreign key to devices.device_id, I can't set the field to null through the UI.
But this SQL query works:
UPDATE devices SET leader_id = NULL WHERE device_id = 'CFBD1001471B';
Here I'm trying to edit the leader_id row with existing data
I try to clear the leader_id column (there is unfortunately no Set to null button, only Select record)
But I end up with the error
