I want to create my primary index using "CREATE UNIQUE INDEX
IDX_PK_NAME..." instead of specify a constraint in my CREATE TABLE.
Mainly so that my primary keys have a name rather than RDB$PRIMARY1453
or whatever.
So this works
CREATE TABLE "LOCATIONS"
(
"LOCATION_REF" "DOM_PK_FIELD",
"LOCATION_NAME" VARCHAR(50),
"LOCATION_SIZE" VARCHAR(25)
);
CREATE TABLE "BUILDINGS"
(
"BUILDING_REF" "DOM_PK_FIELD",
"BUILDING_NAME" VARCHAR(50),
"BUILDING_SIZE" VARCHAR(25),
"LOCATION_REF" INTEGER
);
CREATE UNIQUE INDEX "IDX_PK_LOCATIONS" ON LOCATIONS ("LOCATION_REF");
CREATE UNIQUE INDEX "IDX_PK_BUILDINGS" ON BUILDINGS ("BUILDING_REF");
However when I try to add my foreign key constraint...like this...
ALTER TABLE "BUILDINGS" ADD CONSTRAINT "FK_LOCATIONS_LOCATION_REF"
FOREIGN KEY ("LOCATION_REF") REFERENCES LOCATIONS ("LOCATION_REF") ON
UPDATE CASCADE ON DELETE CASCADE;
I always get a message "Unsuccessful metadata update
STORE RDB$REF_CONSTRAINTS failed
action cancelled by trigger (1) to preserve data integrity
Name of Referential Constraint not defined in constraints table
Statement"
However if I use the PRIMARY KEY CONSTRAINT as follows, it will let me
use my FOREIGN KEY constraint. My question is, can foreign keys only be
tied to primary keys? IE I have to use the primary key constraint and
therefore my primary keys can not have names?
CREATE DOMAIN "DOM_PK_FIELD" AS INTEGER NOT NULL;
CREATE TABLE "LOCATIONS"
(
"LOCATION_REF" "DOM_PK_FIELD",
"LOCATION_NAME" VARCHAR(50),
"LOCATION_SIZE" VARCHAR(25),
CONSTRAINT "PK_LOCATIONS" PRIMARY KEY ("LOCATION_REF")
);
CREATE TABLE "BUILDINGS"
(
"BUILDING_REF" "DOM_PK_FIELD",
"BUILDING_NAME" VARCHAR(50),
"BUILDING_SIZE" VARCHAR(25),
"LOCATION_REF" INTEGER,
CONSTRAINT "PK_BUILDINGS" PRIMARY KEY ("BUILDING_REF")
);
ALTER TABLE "BUILDINGS" ADD CONSTRAINT "FK_LOCATIONS_LOCATION_REF"
FOREIGN KEY ("LOCATION_REF") REFERENCES LOCATIONS ("LOCATION_REF") ON
UPDATE CASCADE ON DELETE CASCADE;
srdan...@hotmail.com
