Aaaand I feel dumb now :)
Thanks a lot: that was the issue.
On Mar 21 2022, at 1:30 pm, Zero <hz0...@gmail.com> wrote:
>
> 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.
