Hello
I have a small derby database on which I am doing some maintenance and I am
facing an odd issue: I cannot enforce a foreign key to a table even if I am
reasonably sure the data is correct.
The database contains an application metadata and is quite small in size
(approx 16 M). Among others, there are two tables that I am trying to better
shape and that are created like this :
CREATE TABLE ACL (
ID BIGINT NOT NULL PRIMARY KEY,
COMPONENT VARCHAR(255),
COMPONENT_CLASS VARCHAR(255),
EDITABLE DECIMAL(15,0),
ENABLED DECIMAL(15,0),
ROLE_ID DECIMAL(15,0) NOT NULL,
VISIBLE DECIMAL(15,0),
UUID VARCHAR(32)
);
CREATE TABLE ROLES (
ID BIGINT NOT NULL PRIMARY KEY,
DESCRIPTION VARCHAR(255),
HELP_DATA BLOB,
HELP_FILENAME VARCHAR(255),
NAME VARCHAR(255),
UUID VARCHAR(32),
OVERRIDE_ADMIN NUMERIC(1,0) DEFAULT 0 NOT NULL
);
Apart from the data type that could be optimized (yes, files withing the db!),
I noticed that the ACL table was missing the FK on the ROLES table. What I was
trying to do was :
ALTER TABLE ACL ADD FOREIGN KEY (ROLE_ID) REFERENCES ROLES(ID);
But I got the infamous error :
"Constraint 'SQL0000000009-c8e244b2-017f-abe9-36f5-000002c54059' is invalid:
there is no unique or primary key constraint on table '"MUIPROMO"."ROLES"' that
matches the number and types of the columns in the foreign key."
The first thing I thought of was some mis-alignment between the two tables so I
checked that the data was matching :
SELECT count(*) FROM ACL A LEFT JOIN ROLES B ON A.ROLE_ID = B.ID WHERE B.ID IS
NULL;
and the result is 0
I tried to check for duplicates on the roles table :
SELECT COUNT(ID),ID FROM ROLES GROUP BY ID HAVING COUNT(ID) > 1
and I get no results (as I expected)
I tried compressing the tables :
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('MUIPROMO','ROLES' ,0);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('MUIPROMO','ACL' ,0);
But I still am facing the issue.
It must be something obvious that I am failing to see...
Any help would greatly be appreciated
Thanks in advance
Marco F.
