Update First Table Primary Key which is Referenced in Second Table

I have 2 Tables Here,

CREATE TABLE first (id SERIAL PRIMARY KEY, name STRING);
CREATE TABLE second (
id SERIAL PRIMARY KEY,
first_id INT,
name STRING,
CONSTRAINT first_fk FOREIGN KEY (first_id) REFERENCES first(id));

Have also Inserted 1 value each in the tables. They get properly inserted into the DB.

INSERT INTO first(name) VALUES (‘karan’);
INSERT INTO second(“name”, first_id) VALUES (‘second name’, 313800012546375681);

Now, I wanted to update the First Table ‘id’ Column with a new value ie. 500 and wanted that to automatically reflect in the second table as well. Is there any method to be able to do something like this?

Thanks a ton.

Hi @karansoi, that feature is being built into the upcoming v2.0 release. Essentially, when you set a foreign key constraint (on CREATE TABLE or ALTER TABLE...ADD COLUMN), you will be able to specify either ON UPDATE CASCADE or ON UPDATE RESTRICT. The former will do what you’re looking for; the latter will prevent a referenced value from being updated. I can’t find the relevant issue for tracking, unfortunately, but @Bram should be able to tell us.

On a related note, ON DELETE CASCADE/RESTRICT is already available in the latest v2.0 alpha release, and we’re working on docs for that already.

That is great, thanks for your quick response. Will be eagerly looking out for the 2.0 Release. Is there any time line when it will be out for production use?

Hey @karansoi, we’re planning on a Spring release. So fairly soon!

I don’t have an issue for tracking it, just an RFC. You can see it here: https://github.com/cockroachdb/cockroach/blob/master/docs/RFCS/20171201_cascade.md

Wow, That is really awesome.
Thank you for the link as well.
PS. Kudos for the Incredible DB y’all are building.

1 Like