db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6661) dblook does not recognize the deferrability of deferrable constraints
Date Tue, 15 Jul 2014 22:31:05 GMT

    [ 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)

Mime
View raw message