db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: Altering IDENTITY Constraint
Date Fri, 06 Jan 2006 07:36:45 GMT
Hi Thomas,

I just wanted to confirm that there is no way to switch the IDENTITY column
from GENERATED ALWAY AS ALWAYS to GENERATED BY DEFAULT and vice versa.

Mamta

On 1/5/06, Thomas J. Taylor <thomas.taylor@itqa.miami.edu> wrote:
>
> Hi There,
>
> I have created two tables in Derby-10.1.1.0:
>
> CREATE TABLE TableA (
>    uid BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL,
>    val VARCHAR(255) UNIQUE,
>    PRIMARY KEY (uid));
>
> CREATE TABLE TableB (
>    uid BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL,
>    val VARCHAR(255),
>    tableA_uid BIGINT NOT NULL,
>    PRIMARY KEY (uid),
>    FOREIGN KEY (tableA_uid) REFERENCES TableA (uid));
>
>
> I need to be able to insert calculated values into the TableA.uid field
> (i.e., implement my own autonumbering); but GENERATED ALWAYS AS IDENTITY
> does not allow this column to be specified, for example, I need to be able
> to run:
>
> INSERT INTO TableA (uid, val) VALUES (1, 'value');
> -- Error: SQL Exception: Attempt to modify an identity column 'DBID'.
>
> What is the easiest way to remove this constraint?
>
> For example,
> - is it possible to ALTER TABLE to change GENERATED ALWAYS to GENERATED BY
> DEFAULT? -- I don't think so
> - is it possible to ALTER TABLE to drop the IDENTITY constraint?
>
> From the SYS.SYSCONSTRAINTS, I have two constraints defined on TableA (the
> PRIMARY KEY constraint on uid and UNIQUE constraint on val). I cannot drop
> the PK constraint without dropping the FK constraint TableB.tableA_uid.
>
> To complicate things:
> 1. My actual problem involves dropping this type of constraint on five
> inter-dependent tables
> 2. The IDENTITY/PRIMARY KEY constraint is not named - so I would need to
> find the constraint name SYS.SYSTABLES and SYS.SYSCONSTRAINTS
> 3. There are several databases that have been created/populated
> a. Each database has a significant amount of data in it
> b. Each database is located on a different computer in another state (that
> I don't have access to)
>     (but I can have someone run an SQL script on the computer)
>
> At this point, I'm thinking that the simplest solution would be to:
> (1) create new tables without the IDENTITY constraint
> (2) copy the data to the new tables
> (3) drop the old tables
> (4) rename the new tables to the old table name
>
> Thanks for your help and advice.
>
> Thomas Taylor
>
>
>

Mime
View raw message