[
https://issues.apache.org/jira/browse/DERBY-6882?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-6882:
---------------------------------
Attachment: derby-6882-01-aa-initialCode.diff
Attaching derby-6882-01-aa-initialCode.diff. This patch adds the new syntax.
This patch is not ready for commit. Tests need to be written and regression
tests need to be run. I have only bothered to implement this feature for the
new-style identity columns which were introduced in Derby 10.10. That is, the
ones which are backed by sequence generators.
With this patch, the following script runs correctly:
{noformat}
connect 'jdbc:derby:memory:db;create=true';
create table t
(
a int generated always as identity,
b int
);
create table s
(
a int generated by default as identity,
b int
);
insert into t(b) values (100);
insert into s(b) values (100);
-- fails
insert into t(a, b) values (-1, -100);
-- succeeds
insert into s(a, b) values (-1, -100);
alter table t alter column a set generated by default;
alter table s alter column a set generated always;
-- succeeds
insert into t(a, b) values (-2, -200);
-- fails
insert into s(a, b) values (-2, -200);
insert into t(b) values (200);
insert into s(b) values (200);
alter table t alter column a set generated always;
alter table s alter column a set generated by default;
-- fails
insert into t(a, b) values (-3, -300);
-- succeeds
insert into s(a, b) values (-3, -300);
insert into t(b) values (300);
insert into s(b) values (300);
select * from t order by a;
select * from s order by a;
{noformat}
> Add functionality to ALTER TABLE: switch from GENERATED ALWAYS to GENERATED
> BY DEFAULT
> --------------------------------------------------------------------------------------
>
> Key: DERBY-6882
> URL: https://issues.apache.org/jira/browse/DERBY-6882
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.12.1.1
> Reporter: Aleksei Kovura
> Assignee: Rick Hillegas
> Labels: features
> Attachments: derby-6882-01-aa-initialCode.diff
>
>
> I'm trying to import data from another Derby database with foreignViews tool
> because system tables got corrupted somehow (see
> https://issues.apache.org/jira/browse/DERBY-6876). Tables contain generated
> ID columns (created as "GENERATED ALWAYS"). Importing data and allowing Derby
> to generate new IDs in generated columns will break relationships between
> tables (old tables have counter gaps there due to deletes - IDs won't match).
> For a clean import without breaking DDL information in DB version control I
> would like to be able to switch between generated types as follows:
>
> ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED BY DEFAULT [ AS IDENTITY]
> ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED ALWAYS [ AS IDENTITY]
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
