You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Marco Ferretti <ma...@gmail.com> on 2022/03/21 11:20:36 UTC
cannot enforce foreign key
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.
Re: cannot enforce foreign key
Posted by Marco Ferretti <ma...@gmail.com>.
Aaaand I feel dumb now :)
Thanks a lot: that was the issue.
On Mar 21 2022, at 1:30 pm, Zero <hz...@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.
Re: cannot enforce foreign key
Posted by Zero <hz...@gmail.com>.
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.