[Vtigercrm-developers] Deleting records from Vtiger modules + tables. Question regarding constraints / Foreign keys

[Sutharsan Jeganathan]

Hi

You need add module primary key to crmentity table's crmid field when
deleting cascading option.

Example, when creating db
CREATE TABLE `vtiger_service` (
  `serviceid` int(11) NOT NULL,
  `service_no` varchar(100) NOT NULL,
  `servicename` varchar(50) NOT NULL,
  `servicecategory` varchar(200) DEFAULT NULL,
  `qty_per_unit` decimal(11,2) DEFAULT '0.00',
  `unit_price` decimal(25,8) DEFAULT NULL,
  `sales_start_date` date DEFAULT NULL,
  `sales_end_date` date DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `discontinued` int(1) NOT NULL DEFAULT '0',
  `service_usageunit` varchar(200) DEFAULT NULL,
  `website` varchar(100) DEFAULT NULL,
  `taxclass` varchar(200) DEFAULT NULL,
  `currency_id` int(19) NOT NULL DEFAULT '1',
  `commissionrate` decimal(7,3) DEFAULT NULL,
  PRIMARY KEY (`serviceid`),
*  CONSTRAINT `fk_1_vtiger_service` FOREIGN KEY (`serviceid`) REFERENCES
`vtiger_crmentity` (`crmid`) ON DELETE CASCADE*
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

If you have to update an existing db

ALTER TABLE users_role_map
ADD CONSTRAINT *fk_1_vtiger_service*FOREIGN KEY (serviceid) REFERENCES
* `vtiger_crmentity` (`crmid`) *


*ON DELETE CASCADE*

*N*eed to do this each table you need and replace "Service" module
with required module.

Note you should have 'FORIEGN_KEY_CHECKS' Parameter in your MySQL server On (1)


*ThanksSutharsan Jeganathan*



On Mon, Oct 24, 2016 at 3:25 AM, socialboostdk <socialboostdk at gmail.com>
wrote:

> Hi there,
>
> Thank you - ok, that sounds very good.
>
> Do you have a list of Contstraints / Foreign keys - or perhaps even the
> required SQL to apply to the database?
>
> Only relevant thing i could find after some searching was this:
> http://code.vtiger.com/alanbell/yetiforce/blob/
> de2eb4a7a5cd0a6e2ae91df905012dac7a65bc25/install/install_schema/scheme.sql
>
> Thanks for any input!
>
> Best,
> Kim
>
> On 23 October 2016 at 17:54, Sutharsan Jeganathan <ajstharsan at gmail.com>
> wrote:
>
>> Hi
>>
>> The FK is enough. Example vtiger_acount.accountid cascaded on delet of
>> vtiger_crmentity.crmid.  This has to be done on each entity module tables,
>> then it will work fine.
>>
>>
>> Thanks
>> Sutharsan Jeganathan
>>
>> On Fri, Oct 21, 2016 at 3:48 PM, socialboostdk <socialboostdk at gmail.com>
>> wrote:
>>
>>> Yes - i know its a known issue, unfortunately.
>>>
>>> Was just wondering if the constraints/FK's did the trick, or we had to
>>> do some external script to clean it up while waiting... :)
>>>
>>> On 21 October 2016 at 11:51, cryptic <mark at markcox.co.uk> wrote:
>>>
>>>> This issue was raised quite a while back-:
>>>>
>>>> http://code.vtiger.com/vtiger/vtigercrm/issues/117
>>>>
>>>>
>>>>
>>>> --
>>>> View this message in context: http://vtiger-crm.2324883.n4.n
>>>> abble.com/Vtigercrm-developers-Deleting-records-from-Vtiger-
>>>> modules-tables-Question-regarding-constraints-Fores-tp20011p20012.html
>>>> Sent from the vtigercrm-developers mailing list archive at Nabble.com.
>>>> _______________________________________________
>>>> http://www.vtiger.com/
>>>>
>>>
>>>
>>> _______________________________________________
>>> http://www.vtiger.com/
>>>
>>
>>
>> _______________________________________________
>> http://www.vtiger.com/
>>
>
>
> _______________________________________________
> http://www.vtiger.com/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20161024/c5c505d0/attachment.html>