[
https://issues.apache.org/jira/browse/DERBY-6661?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14062773#comment-14062773
]
Knut Anders Hatlen commented on DERBY-6661:
-------------------------------------------
Thanks for adding this improvement, Rick.
I ran some quick tests, and dblook now seems to produce the correct DDL as far
as I can tell.
One little nit: The patch seems to be generous with the blanks around
DEFERRABLE and INITIALLY DEFERRED. For example, given the database created by
this script:
{code:sql}
connect 'jdbc:derby:db;create=true';
create table t1(x int, constraint t1_pk primary key (x));
create table t2(x int, constraint t2_fk foreign key (x) references t1 initially
deferred);
create table t3(x int, constraint t3_fk foreign key (x) references t1 initially
immediate);
create table t4(x int, constraint t4_fk foreign key (x) references t1
deferrable);
{code}
I see this output from dblook:
{code:sql}
-- ----------------------------------------------
-- DDL Statements for tables
-- ----------------------------------------------
CREATE TABLE "APP"."T2" ("X" INTEGER);
CREATE TABLE "APP"."T1" ("X" INTEGER NOT NULL);
CREATE TABLE "APP"."T4" ("X" INTEGER);
CREATE TABLE "APP"."T3" ("X" INTEGER);
-- ----------------------------------------------
-- DDL Statements for keys
-- ----------------------------------------------
-- PRIMARY/UNIQUE
ALTER TABLE "APP"."T1" ADD CONSTRAINT "T1_PK" PRIMARY KEY ("X");
-- FOREIGN
ALTER TABLE "APP"."T2" ADD CONSTRAINT "T2_FK" FOREIGN KEY ("X") REFERENCES
"APP"."T1" ("X") ON DELETE NO ACTION ON UPDATE NO ACTION DEFERRABLE INITIALLY
DEFERRED ;
ALTER TABLE "APP"."T4" ADD CONSTRAINT "T4_FK" FOREIGN KEY ("X") REFERENCES
"APP"."T1" ("X") ON DELETE NO ACTION ON UPDATE NO ACTION DEFERRABLE ;
ALTER TABLE "APP"."T3" ADD CONSTRAINT "T3_FK" FOREIGN KEY ("X") REFERENCES
"APP"."T1" ("X") ON DELETE NO ACTION ON UPDATE NO ACTION;
{code}
Notice that there are two blanks between DEFERRABLE and INITIALLY DEFERRED, and
there is an extra blank before the semicolon in the ALTER TABLE statements for
all the deferrable constraints.
> dblook does not recognize the deferrability of deferrable constraints
> ---------------------------------------------------------------------
>
> Key: DERBY-6661
> URL: https://issues.apache.org/jira/browse/DERBY-6661
> Project: Derby
> Issue Type: Bug
> Components: Tools
> Affects Versions: 10.11.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Fix For: 10.11.0.0
>
> Attachments: derby-6661-01-aa-recreateDeferrableConstraints.diff
>
>
> If you create a table with a deferrable primary key constraint and then run
> dblook, the recreation ddl does not flag the primary key as deferrable. I
> suspect that this applies to other kinds of constraints as well.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
