Hi,
I´m studying the database model in weberpdemo and I noticed that there are some duplicated foreign keys. For Example:
In stockrequest:
CONSTRAINT `stockrequest_ibfk_1` FOREIGN KEY (`loccode`) REFERENCES `locations` (`loccode`),
CONSTRAINT `stockrequest_ibfk_2` FOREIGN KEY (`departmentid`) REFERENCES `departments` (`departmentid`),
CONSTRAINT `stockrequest_ibfk_3` FOREIGN KEY (`loccode`) REFERENCES `locations` (`loccode`),
CONSTRAINT `stockrequest_ibfk_4` FOREIGN KEY (`departmentid`) REFERENCES `departments` (`departmentid`)
It´s cause no errors, but can let database operations more slow. I suggest a review in a future version.
Regards.
Hi, Alex,
Thank you very much!
It's fixed!
Best regards!
Exson
Hello.
Looking at information schema, follow the complete list of duplicated foreign keys:
use information_schema;
SELECT for_name, ref_name, for_col_name, ref_col_name, count(*)
FROM `INNODB_SYS_FOREIGN` f, `INNODB_SYS_FOREIGN_COLS` fc
where f.id = fc.id
and for_name like 'weberp/%'
group by for_name, ref_name, for_col_name, ref_col_name
having count(*) > 1
order by for_name;
weberp/internalstockcatrole weberp/securityroles secroleid secroleid 2
weberp/internalstockcatrole weberp/stockcategory categoryid categoryid 2
weberp/stockitemproperties weberp/stockcatproperties stkcatpropid stkcatpropid 3
weberp/stockitemproperties weberp/stockmaster stockid stockid 3
weberp/stockmovestaxes weberp/stockmoves stkmoveno stkmoveno 3
weberp/stockrequest weberp/departments departmentid departmentid 2
weberp/stockrequest weberp/locations loccode loccode 2
weberp/stockrequestitems weberp/stockmaster stockid stockid 2
weberp/stockrequestitems weberp/stockrequest dispatchid dispatchid 2
So, here is the patch:
use weberp;
alter table internalstockcatrole DROP FOREIGN KEY internalstockcatrole_ibfk_4;
alter table internalstockcatrole DROP FOREIGN KEY internalstockcatrole_ibfk_3;
alter table stockitemproperties DROP FOREIGN KEY stockitemproperties_ibfk_4;
alter table stockitemproperties DROP FOREIGN KEY stockitemproperties_ibfk_6;
alter table stockitemproperties DROP FOREIGN KEY stockitemproperties_ibfk_3;
alter table stockitemproperties DROP FOREIGN KEY stockitemproperties_ibfk_5;
alter table stockmovestaxes DROP FOREIGN KEY stockmovestaxes_ibfk_3;
alter table stockmovestaxes DROP FOREIGN KEY stockmovestaxes_ibfk_4;
alter table stockrequest DROP FOREIGN KEY stockrequest_ibfk_4;
alter table stockrequest DROP FOREIGN KEY stockrequest_ibfk_3;
alter table stockrequestitems DROP FOREIGN KEY stockrequestitems_ibfk_4;
alter table stockrequestitems DROP FOREIGN KEY stockrequestitems_ibfk_3;
The file weberpchina.sql has duplicated foreign key only on table internalstockcatrole.
The scripts default.sql, demo.sql and weberpchina.sql shoud be updated removing dropped foreign keys above.
Regards.
Hi, Alex,
Thanks!
I'll fix it later.
Best regards!
Exson