After testing the issue below, I am still troubled. I recreated the
domain/table from the metadata below and the primary key behaved as
expected, as unique. I cannot reproduce the uniqueness failure I
reported, however I still have the original customers table --
exactly as shown below -- that allows duplicate primary keys! The
construction of the table was straightforward; I did not touch any
system entries or do anything unusual. I presume that somehow the
unique constraint got lost from the system tables. The solution is
to manually check that primary keys are performing as expected, but
it is still worrying that it was allowed to happen in the first place.

I have since backed-up/restored the faulty db and received this error
message:

error 335544342
Action cancelled by trigger (3) to preserve data integrity
Cannot deactivate primary index



Regards,
Andrew Ferguson


-----

From: "csswa" <csswa@y...>
Date: Fri Feb 1, 2002 12:36 am
Subject: Primary key not set to unique

ADVERTISEMENT

I was surprised to discover that a primary key created from a domain
was not created automatically as unique.

The domain used:

CREATE DOMAIN "DOM_KEY" AS NUMERIC(18, 0)
check (value > 0) NOT NULL;

The metadata for the table (note, F_PK field DDL entered as 'f_pk
dom_key primary key'):

CREATE TABLE "T_CUSTOMERS"
(
"F_PK" "DOM_KEY",
"F_CODE" "DOM_CODE",
"F_NAME" "DOM_STRING1" NOT NULL,
"F_ADDRESS" NUMERIC(18, 0),
"F_COMMENTS" "DOM_MEMO",
"F_RECGENSTAMP" "DOM_RECGENSTAMP",
PRIMARY KEY ("F_PK"),
UNIQUE ("F_CODE")
);

Despite being a primary key, I can enter duplicate F_PK values into
this table. Do I need to manually define a primary key as unique in
this situation? That doesn't seem right...

Regards,
Andrew Ferguson
-FBrc2, ibconsole, NT4sp6, AMDduron850, 512ram