db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas J. Taylor" <thomas.tay...@itqa.miami.edu>
Subject Altering IDENTITY Constraint
Date Thu, 05 Jan 2006 20:36:16 GMT
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