On 3/21/22 12:20, Marco Ferretti wrote:
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.
I think for a foreign key constraint the types must be identical.
ID is BigInt and ROLE_ID is Decimal.
Harm-Jan Z.
