At 01:49 PM 12-01-02 -0800, you wrote:

>I drop all indexes, deleted all rows still the same error?
>does someone know why?
>Daniel
>
>At 12:54 PM 12/01/2002 -0800, you wrote:
>>Hi all,
>>I'm wanting to add a primary key to a table? I drop the key field and
>>re-created as
>>
>>ADD sourceid VARCHAR(4) NOT NULL
>>
>>then I tried to add
>>
>>ALTER TABLE SOURCE_ID
>>ADD PRIMARY KEY (sourceid);
>>
>>I get this error,
>>
>>ISC ERROR CODE:335544349
>>ISC ERROR MESSAGE:
>>attempt to store duplicate value (visible to active transactions) in unique
>>index "RDB$PRIMARY5"
>>null segment of UNIQUE KEY
>>
>>what am I doing wrong?

Daniel,
This isn't an IBO issue and should be posted in the ib-support list...

You've missed a few steps but you will bump into another problem before you solve this.

You can't do ALTER TABLE ATABLE ADD NEWCOLUMN SOMETYPE NOT NULL on a table that is already populated because a new column would be full of NULLs.

Even if you could create a NOT NULL column full of NULLs, you couldn't put a primary key on it because it contains NULLs !

You will need to do several steps:

1. Add the column to your original table without any constraints.
2. Next, you must fully populate the column with unique values.
3. Next, you must create a new table containing columns with definitions matching the original, except that you will add the NOT NULL constraint to the column you intend to use for the primary key, as a temporary holding place for your data.
4. Next, pump all the data across from the original table to the new one.
5. Drop the original table.
6. Create a new table with the same name and exact specifications as the original one. You can include the primary key in this specification if you want to.
7. Pump the data from the second table into this new one.
8. Drop the temporary table.

Varchar(4) seems too small for a unique key unless this is just a little control table of some sort...

Anyhow, to populate this column you will need to work out some algorithm for constructing this unique value, and use a stored procedure to put the values into the table (or enter them manually).

After that, you will need some multi-user-safe mechanism to construct a unique key for every new record.

Is there a special reason why you can't use a generator for your primary key?


regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at www.ibobjects.com