Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
duplicated foreign keys
11-18-2015, 05:00 AM,
#1
duplicated foreign keys
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.
Reply
11-18-2015, 08:56 AM,
#2
RE: duplicated foreign keys
Hi, Alex,

Thank you very much!

It's fixed!

Best regards!

Exson
Reply
12-08-2015, 11:40 PM,
#3
RE: duplicated foreign keys
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.
Reply
12-09-2015, 11:39 AM,
#4
RE: duplicated foreign keys
Hi, Alex,

Thanks!

I'll fix it later.

Best regards!

Exson
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)