Reverse Engineering Foreign Key Cascade Missing
A Java SQL client for any JDBC compliant database
Brought to you by:
colbell,
gerdwagner
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.
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 !
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
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 .
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
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.