webERP Forum
duplicated foreign keys - Printable Version

+- webERP Forum (http://www.weberp.org/forum)
+-- Forum: webERP Discussion (http://www.weberp.org/forum/forumdisplay.php?fid=1)
+--- Forum: Problems / Bugs? (http://www.weberp.org/forum/forumdisplay.php?fid=8)
+--- Thread: duplicated foreign keys (/showthread.php?tid=2614)



duplicated foreign keys - alexfigueiro - 11-18-2015

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.


RE: duplicated foreign keys - Exsonqu_Qu - 11-18-2015

Hi, Alex,

Thank you very much!

It's fixed!

Best regards!

Exson


RE: duplicated foreign keys - alexfigueiro - 12-08-2015

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.



RE: duplicated foreign keys - Exsonqu_Qu - 12-09-2015

Hi, Alex,

Thanks!

I'll fix it later.

Best regards!

Exson