#397 Reverse Engineering Foreign Key Cascade Missing

2.3final
closed-fixed
Rob Manning
7
2006-10-10
2006-07-10
Anonymous
No

Hi,

When using any option that make reverse engineering
of a table, i.e.: Objects_Tree - > <TableName> -> PopUp
-> "Create Table Script" Option, I've noticed that the
CASCADE never appears on the script generated.

The same occurs when I try the "Show DDL" on Graphics
plugin. With the Oracle plugin I have the same effect.

P.S.: Sorry my pur english :]

Bye, thanks.

Discussion

  • Rob Manning
    Rob Manning
    2006-09-24

    • assigned_to: nobody --> manningr
    • status: open --> pending
     
  • Rob Manning
    Rob Manning
    2006-09-24

    Logged In: YES
    user_id=1287991

    What do mean by "the CASCADE never appears"? When I create a
    table script for a table whose columns reference other table's
    primary keys (via foreign key), the foreign key constraint
    alter statements appear after the table definition. It seems
    to work fine (at least my test was on Oracle). Are you
    saying that these alter statements do not appear for you?
    Perhaps you could give us a sample script that creates the
    data and steps to perform and what you believe is missing
    in the output. Also, please tell us what database/driver
    versions you are using.

     
  • Logged In: NO

    Ok, here's the sample script:

    create table parent (id integer not null primary key,
    name varchar2(10) not null);

    create table child (refId integer not null,
    child_name varchar2(10));

    alter table child add constraint fk_child_1 foreign key
    (refId) references parent(id) on delete cascade;

    The steps...

    1 - On Object Tree, select the "Child" Table

    2 - Activate the popup menu and select "Create Table Script"

    The returned data:

    CREATE TABLE CHILD
    (
    REFID NUMBER(22) not null,
    CHILD_NAME VARCHAR2(10)
    );

    ALTER TABLE CHILD
    ADD CONSTRAINT FK_CHILD_1
    FOREIGN KEY (REFID)
    REFERENCES PARENT(ID);

    Ok, looking in the alter table command, the "ON DELETE
    CASCADE" clause is missing.

    I Have performed the test with Oracle 10g R2 and JDBC (Thin)
    Driver version 10.2.0.1.0.

    But this issue happens with Firebird and happens too when
    using the "Script Table" popup option of the Graphic Plugin.

    Thanks !

     
    • status: pending --> open
     
  • Rob Manning
    Rob Manning
    2006-09-28

    Logged In: YES
    user_id=1287991

    Thank you very much for providing the example. I can see
    now what you mean. I think we need a configuration section
    in the SQLScripts tab for this. "ON DELETE CASCADE" is
    what the SQL-92 refers to as a "referential action". There
    are actually eight of these (2 are default). They look like

    ON DELETE { no action | cascade | set default | set null }
    ON UPDATE { no action | cascade | set default | set null }

    So to make this useful generally and not just for those
    people who want deletes to cascade, we'll have to add
    a couple of checkboxes and some dropdowns in the SQLScripts
    tab. I'll pick that task up next unless something more
    critical comes along.

    Rob

     
  • Rob Manning
    Rob Manning
    2006-09-28

    • milestone: 126228 --> 641636
    • labels: 779787 --> SQL Scripts Plugin
     
  • Rob Manning
    Rob Manning
    2006-09-29

    • status: open --> closed-fixed
     
  • Rob Manning
    Rob Manning
    2006-09-29

    Logged In: YES
    user_id=1287991

    Fixed in CVS. Now the behavior is configurable using
    the Global Preferences dialog in the SQLScripts tab.

    Rob

     
  • Logged In: NO

    Hi.

    I've downloaded the version 2.3 final and tested the "Create
    Script Table".

    IMO, this is not the correct way this function must work.

    I think that, when I activate de "Create Table Script". The
    code generated must show the real table in the moment, not
    show something that was choosed in configuration.

    Maybe could be make an option in configuration to allow
    squirrel show the state of the table, not one of the choosen .

     
  • Rob Manning
    Rob Manning
    2006-10-09

    Logged In: YES
    user_id=1287991

    Sure that is a valid point. I only wish that had occurred
    to me when I was fixing the bug ;) I'll change the
    configuration so that it says "Override" instead of "Add" and
    set the defaults to unchecked. If the overrides aren't
    checked, then the script will be created based upon what's
    available in the DatabaseMetaData. Thanks for filing this
    bug. This will go into 2.4 RC1.

    Rob

     
  • Rob Manning
    Rob Manning
    2006-10-09

    • status: closed-fixed --> open-accepted
     
  • Rob Manning
    Rob Manning
    2006-10-10

    • milestone: 641636 --> 2.3final
    • status: open-accepted --> closed-fixed
     
  • Rob Manning
    Rob Manning
    2006-10-10

    • priority: 5 --> 7
     
  • Rob Manning
    Rob Manning
    2006-10-10

    Logged In: YES
    user_id=1287991

    Fixed in CVS. changed the function of the configurable
    ref actions checkboxes for SQLScript plugin. Now, they
    will override what is contained in the DatabaseMetaData
    if checked. If not checked, we now use whatever the
    database tells us the ref action is (before we weren't
    bothering to check the with the database. Now we do,
    by default)

    Rob

     
  • Logged In: NO

    Hi, I've tested the new feature(or bug fix, if you prefer) and it works fine.

    But, the problem persists when using the Graph plugin. When use the "Show DDL" or "DDL script" options the result is an "ALTER TABLE..." without the ON DELETE(or CASCADE) in both cases.