--- In firebird-support@yahoogroups.com, "kapilpatil84firebird"
<kapilpatil84firebird@y...> wrote:

>
> We Normally provide PRIMARY KEY in every Table.
> But Sometimes we forget or for some reason we do not provide primary
> key in table.
> In that case it is very difficult to delete one of the two duplicate
> records
>
> Just an Example:
> ======================================
> DEPT_ID DEPT_NAME
> 10 CHEMICAL
> 10 CHEMICAL
> 10 CHEMICAL
> ======================================
>
> Here I Entered Three duplicate records my mistake and I want to keep
> only One Unique Record, So if would have been Any Feature to Uniquely
> Identify the Row I would be able to Delete that row using that UNIQUE
> ID as Oracle Supports.
>

I know I am a bit late to the party, but why not kill two birds with
one stone. Yes you can use the RDB$KEY as long as you understand the
caveats (ie. It is a transient value), but why not this even easier
solution?

From above, I assume you have a table called Department.

Backup first (as you should before any DDL changes).

-----

ALTER TABLE DEPARTMENT ADD ID INTEGER;
CREATE GENERATOR GEN_DEPARTMENTID;
COMMIT;
ALTER TABLE DEPARTMENT ADD CONSTRAINT PK_DEPARTMENT PRIMARY KEY (ID);
COMMIT;

-- Ensure new rows have an ID. This also makes it backwards compatible
-- without any code changes or recompiles.

SET TERM ^ ;

CREATE TRIGGER DEPARTMENT_BI FOR DEPARTMENT
ACTIVE BEFORE INSERT POSITION 1
AS
BEGIN
IF (NEW.ID IS NULL) THEN
BEGIN
NEW.ID = GEN_ID(GEN_DEPARTMENTID,1);
END
END
^

-- Fix existing rows

CREATE PROCEDURE TMP_FIXDEPARTMENT AS
BEGIN
UPDATE DEPARTMENT SET
ID = GEN_ID(GEN_DEPARTMENTID, 1)
WHERE ID IS NULL;
END
^

SET TERM ;
^

COMMIT WORK;

EXECUTE PROCEDURE TMP_FIXDEPARTMENT;

COMMIT WORK;

DROP PROCEDURE TMP_FIXDEPARTMENT;

-----

Now you have an ID field and each record is unique!

Adam