9 Replies - 625 Views - Last Post: 24 March 2018 - 03:17 AM Rate Topic: -----

#1 Foobarer   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 269
  • Joined: 28-March 17

How to make a column update on delete on other table ?

Posted 23 March 2018 - 03:55 PM

I believe this has to do with constraints, I've never used them so far, but I think this is the case, I just don't know how to set it up:

I have 2 tables: 'users' table and 'profile_images' table.
In the 'users' table there is an 'image_id' column with the the id of the image in the 'profile_images' table.
This 'image_id' is the primary key in 'profile_images'.

I want to update the value of 'image_id' to 0 (or null) on the 'users' table in case a user deletes the image (it deletes the row from profile_images)

thanks!!

This post has been edited by Foobarer: 23 March 2018 - 03:57 PM


Is This A Good Question/Topic? 0
  • +

Replies To: How to make a column update on delete on other table ?

#2 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2906
  • View blog
  • Posts: 11,327
  • Joined: 03-December 12

Re: How to make a column update on delete on other table ?

Posted 23 March 2018 - 04:06 PM

You would have to do that through code or using a trigger. The constraint cascade would be to delete the record. So, when a user deletes their profile, it would retrieve every record constrained by it and delete those as well.
Was This Post Helpful? 1
  • +
  • -

#3 Foobarer   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 269
  • Joined: 28-March 17

Re: How to make a column update on delete on other table ?

Posted 23 March 2018 - 04:09 PM

Alright, I just thought that constraints can have customized options like setting columns. If not I will simply do it through code. Thanks!
Was This Post Helpful? 0
  • +
  • -

#4 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2906
  • View blog
  • Posts: 11,327
  • Joined: 03-December 12

Re: How to make a column update on delete on other table ?

Posted 23 March 2018 - 04:11 PM

Constraints have limited options. Triggers on the other hand give more options. And considering this will be on delete, that is a specific trigger. So, that would be where I would go.
Was This Post Helpful? 1
  • +
  • -

#5 Foobarer   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 269
  • Joined: 28-March 17

Re: How to make a column update on delete on other table ?

Posted 23 March 2018 - 04:22 PM

Oh I didn't read the post correctly, so there are also things called triggers? Any nice information /tutorial on that and the SQL syntax involved?
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6806
  • View blog
  • Posts: 28,134
  • Joined: 12-December 12

Re: How to make a column update on delete on other table ?

Posted 24 March 2018 - 12:15 AM

For which database? The first place to visit is the official documentation for your database of choice.
Was This Post Helpful? 2
  • +
  • -

#7 Foobarer   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 269
  • Joined: 28-March 17

Re: How to make a column update on delete on other table ?

Posted 24 March 2018 - 01:02 AM

It's MariaDB (InnoDB Engine)
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6806
  • View blog
  • Posts: 28,134
  • Joined: 12-December 12

Re: How to make a column update on delete on other table ?

Posted 24 March 2018 - 01:13 AM

So my first step would be a search "mariadb triggers".
Was This Post Helpful? 1
  • +
  • -

#9 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6806
  • View blog
  • Posts: 28,134
  • Joined: 12-December 12

Re: How to make a column update on delete on other table ?

Posted 24 March 2018 - 01:51 AM

Out of a little curiosity I typed "mariadb on delete" and interestingly there is an ON DELETE SET NULL clause.
Was This Post Helpful? 0
  • +
  • -

#10 Foobarer   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 269
  • Joined: 28-March 17

Re: How to make a column update on delete on other table ?

Posted 24 March 2018 - 03:17 AM

Oh haha I need to look better. I am also using PHPMyAdmin, and it has a GUI to let you select between ON DELTE: RESTRICT/ CASCADE/ NO ACTION/ SET NULL <<< I somehow managed to skip that! Thank you :bigsmile:/>/>

This post has been edited by Foobarer: 24 March 2018 - 03:17 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1