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 |