Hi,
Getting a weird warning when running the mysql upgrade checker to 8.4
I’m getting several errors on foreign keys referencing a primary key on a single table.
alfresco_up.fk_alf_store_root - invalid foreign key defined as
‘alf_store(root_node_id)’ references a non unique key at table ‘alf_node’.
alfresco_up.fk_alf_sub_node - invalid foreign key defined as
‘alf_subscriptions(node_id)’ references a non unique key at table ‘alf_node’.
alfresco_up.fk_alf_sub_user - invalid foreign key defined as
‘alf_subscriptions(user_node_id)’ references a non unique key at table
‘alf_node’.
alfresco_up.fk_alf_usaged_n - invalid foreign key defined as
‘alf_usage_delta(node_id)’ references a non unique key at table ‘alf_node’.
Solutions:
-
Convert non unique key to unique key if values do not have any duplicates.
In case of foreign keys involving partial columns of key, create composite
unique key containing all the referencing columns if values do not have any
duplicates. -
Remove foreign keys referring to non unique key/partial columns of key.
-
In case of multi level references which involves more than two tables
change foreign key reference.
Column being referenced is a primary key and therefore should be unique by default.
I also checked if there were actual duplicates somehow on the column being referenced but they are all unique.
Column name is “id”
table create statement is as follows:
| alf_node | CREATE TABLE alf_node
(
id
bigint NOT NULL AUTO_INCREMENT,
version
bigint NOT NULL,
store_id
bigint NOT NULL,
uuid
varchar(36) NOT NULL,
transaction_id
bigint NOT NULL,
type_qname_id
bigint NOT NULL,
locale_id
bigint NOT NULL,
acl_id
bigint DEFAULT NULL,
audit_creator
varchar(255) DEFAULT NULL,
audit_created
varchar(30) DEFAULT NULL,
audit_modifier
varchar(255) DEFAULT NULL,
audit_modified
varchar(30) DEFAULT NULL,
audit_accessed
varchar(30) DEFAULT NULL,
PRIMARY KEY (id
),
UNIQUE KEY store_id
(store_id
,uuid
),
KEY idx_alf_node_txn_type
(transaction_id
,type_qname_id
),
KEY fk_alf_node_acl
(acl_id
),
KEY fk_alf_node_store
(store_id
),
KEY idx_alf_node_tqn
(type_qname_id
,store_id
,id
),
KEY fk_alf_node_loc
(locale_id
),
KEY idx_alf_node_mdq
(store_id
,type_qname_id
),
KEY idx_alf_node_cor
(audit_creator
,store_id
,type_qname_id
),
KEY idx_alf_node_crd
(audit_created
,store_id
,type_qname_id
),
KEY idx_alf_node_mor
(audit_modifier
,store_id
,type_qname_id
),
KEY idx_alf_node_mod
(audit_modified
,store_id
,type_qname_id
),
CONSTRAINT fk_alf_node_acl
FOREIGN KEY (acl_id
) REFERENCES alf_access_control_list
(id
) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk_alf_node_loc
FOREIGN KEY (locale_id
) REFERENCES alf_locale
(id
) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk_alf_node_store
FOREIGN KEY (store_id
) REFERENCES alf_store
(id
) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk_alf_node_tqn
FOREIGN KEY (type_qname_id
) REFERENCES alf_qname
(id
) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk_alf_node_txn
FOREIGN KEY (transaction_id
) REFERENCES alf_transaction
(id
) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=215405 DEFAULT CHARSET=utf8 |