Forum Stats

  • 3,873,267 Users
  • 2,266,528 Discussions
  • 7,911,489 Comments

Discussions

Meaning of cascade constraint

terrencecahill
terrencecahill Member Posts: 10
edited Aug 11, 2019 7:04PM in General Database Discussions

I read the oracle docs about constraints and I dont quite understand the meaing of the sentence that I put in bold:

CASCADE CONSTRAINTS

Specify CASCADE CONSTRAINTS if you want to drop all foreign key constraints that refer to the primary and unique keys defined on the dropped columns as well as all multicolumn constraints defined on the dropped columns. If any constraint is referenced by columns from other tables or remaining columns in the target table, then you must specify CASCADE CONSTRAINTS. Otherwise, the statement aborts and an error is returned.

How can a constraint be rerenced by a column?  I thought only a column could by referenced by another one when creating a constraint like a foreign key contraint.

Could someone explain me what they mean?

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,736 Red Diamond
    edited Aug 5, 2019 5:54PM Answer ✓

    Hi,

    Your understanding is correct; the documentation is worded in a confusing way there.  They are talking about foreign key constraints.

    Technically, a foreign key constraint references a unique constraint (or a primary key constraint, but I'll just say "unique" from now on).  If you create a unique constraint on the deptno column of a table named dept, then you can create foreign key constraint on a column called parent_deptno (either in the dept table or some other table) that references the unique constraint.  In everyday speech, people usually don't talk about the foreign key constraint referencing the unique constraint; they say that the parent_deptno column references the deptno column.  The documentation is mixing the two nomenclatures there.

    terrencecahill

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,736 Red Diamond
    edited Aug 5, 2019 5:54PM Answer ✓

    Hi,

    Your understanding is correct; the documentation is worded in a confusing way there.  They are talking about foreign key constraints.

    Technically, a foreign key constraint references a unique constraint (or a primary key constraint, but I'll just say "unique" from now on).  If you create a unique constraint on the deptno column of a table named dept, then you can create foreign key constraint on a column called parent_deptno (either in the dept table or some other table) that references the unique constraint.  In everyday speech, people usually don't talk about the foreign key constraint referencing the unique constraint; they say that the parent_deptno column references the deptno column.  The documentation is mixing the two nomenclatures there.

    terrencecahill
  • terrencecahill
    terrencecahill Member Posts: 10
    edited Aug 6, 2019 4:59PM

    Ok, thank you for your answer. It seems to be clear for me now. So what they actually mean is that : If any primary key or unique key is referenced by columns from other tables or remaining columns of the parent table, then you must specify CASCADE CONSTRAINTS. Is this correct?

    Also, what about other types of constraints? Is CASCADE CONSTRAINT not necessary when trying to drop a column on which a CHECK constraint (including NULL constraint) is defined or a column referenced in a CHECK constraint defined on another column of the same table? (I dont have a database at hand to try it now...)

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,736 Red Diamond
    edited Aug 6, 2019 5:52PM

    Hi,

    terrencecahill wrote:Ok, thank you for your answer. It seems to be clear for me now. So what they actually mean is that : If any primary key or unique key is referenced by columns from other tables or remaining columns of the parent table, then you must specify CASCADE CONSTRAINTS. Is this correct?

    Yes, I think that's a better way of saying it.

    Also, what about other types of constraints? Is CASCADE CONSTRAINT not necessary when trying to drop a column on which a CHECK constraint (including NULL constraint) is defined or a column referenced in a CHECK constraint defined on another column of the same table? (I dont have a database at hand to try it now...)

    That applies to CHECK constraints that involve multiple columns.  For example, if you have a constraint like:

    CHECK (birth_dt < hire_dt)

    then you must use CASCADE CONSTRAINTS to drop either one of those columns.  It doesn't apply to CHECK constraints that only involve one column.

    NOT NULL constraints never involve multiple columns, so you don't need CASCADE CONSTRAINTS if the only constraint is a NOT NULL constraint.

    terrencecahill
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    edited Aug 7, 2019 4:29AM

    It's very hard to be accurate and succinct - especially when the product keeps changing.

    I think in this case "less is more", the following looks sufficient to me:

    If any constraint referencing the specified column(s) is referenced by other columns in the target table, or by other tables, then you must specify CASCADE CONSTRAINTS.

    The text as supplied seems to be saying the same thing twice (and badly), giving the impression that someone must have read an earlier version of the document and decided to "clarify" it by adding more rather than completely rewriting the original.

    Regards

    Jonathan Lewis

  • terrencecahill
    terrencecahill Member Posts: 10
    edited Aug 7, 2019 3:50PM

    Thanks for your input Jonathan, but the way your sentence is worded doesn't seem less confusing to me than the one in the oracle docs.

    If any constraint referencing the specified column(s) is referenced by other columns in the target table, or by other tables, then you must specify CASCADE CONSTRAINTS.

    The reason is that yours still mixes the two nomenclatures as pointed out in Frank Kulash answer to my question.

    Also, I agree it's very hard to be accurate and succint. So why even try if it could be misleading? There are only 6 types of constraints (NOT NULL, CHECK, PK, FK, UNIQUE, REF).  Considering oracle has hundreds of pages of documentation, would'nt it be less confusing for readers if the doc said clearly how CASCADE CONSTRAINTS works regarding each one of them? Do the way constraints work change regurlary as new Oracle DB versions are released?

    Regards.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    edited Aug 8, 2019 5:03AM
    terrencecahill wrote:Also, I agree it's very hard to be accurate and succint. So why even try if it could be misleading? 

    Succinct is good because that's how Oracle kept the manuals down to a few tens of thousands of pages. Adding a couple more pages to explain in detail one option of one variant of the alter table command - and then doing the same for everything other little detail - would lead to a set of manuals that was impossible to maintain and virtually impossible to use.

    To my mind the most significant problem with the original extract is that it says the same thing twice - but in such a way that the less well-informed reader might think that the second sentence was supplying extra information and waste time trying to work out what that extra information was. That's why I made the point about succinctness.

    Regards

    Jonathan Lewis

    terrencecahill
  • terrencecahill
    terrencecahill Member Posts: 10
    edited Aug 11, 2019 7:03PM
    Jonathan Lewis a écrit:terrencecahill wrote:Also, I agree it's very hard to be accurate and succint. So why even try if it could be misleading? Succinct is good because that's how Oracle kept the manuals down to a few tens of thousands of pages. Adding a couple more pages to explain in detail one option of one variant of the alter table command - and then doing the same for everything other little detail - would lead to a set of manuals that was impossible to maintain and virtually impossible to use.

    I don't know... Perhaps, or perhaps not if it's done properly...

    Jonathan Lewis a écrit:To my mind the most significant problem with the original extract is that it says the same thing twice - but in such a way that the less well-informed reader might think that the second sentence was supplying extra information and waste time trying to work out what that extra information was. That's why I made the point about succinctness.

    I have to admit that was one of the reason I asked the question.

  • User_G0MCD
    User_G0MCD Member Posts: 1 Green Ribbon

    Cascade constraint is used when we are dropping a column X that is referenced to another column A when the referenced column A either has a unique constraint or is a primary key.

    If A has a not null or check constraint, then no need to specify cascade constraint, and if there is another column B such that there is a check constraint comparing A and B then there we have to specify cascade constraint while dropping column A