Skip to Main Content
  • Questions
  • Table TTL and foreign key relationships


Question and Answer

Chris Saxon

Thanks for the question.

Asked: May 27, 2023 - 4:30 pm UTC

Last updated: May 30, 2023 - 1:53 pm UTC

Version: 21c

Viewed 1000+ times

You Asked

I would like my schema to delete rows automatically after a certain number of days.
Therefore, I was planning to use this statement for example:


However, I'm unable to find specifics in the TTL documentation about how FK relationships are handled in this scenario.

Will TTL automatically ignore these FK relationships when it reaches that date and expire these rows anyway?


Will TTL fail, as a delete would fail, because these FK relationships exist and need to be removed first?

and Chris said...

There is no USING TTL clause in Oracle Database, so I'm not sure what you're referring to!

If you want to remove data older than N days/weeks/months/... you'll need to write a process to do this.

Doing this respects foreign keys, so you must first remove the child rows.

You can have the database do this automatically by declaring the foreign keys as ON DELETE CASCADE or ON DELETE SET NULL. These options remove the child rows or set the FK columns in child tables to null respectively. Take care doing this; it makes it easy to accidentally remove/nullify large numbers of rows!

More to Explore


New to good database design? Check out Chris Saxon's full fundamentals class.