<div dir="ltr"><div><div><div>Hi<br><br></div>You need add module primary key to crmentity table's crmid field when deleting cascading option.<br><br></div>Example, when creating db<br>CREATE TABLE `vtiger_service` (<br> `serviceid` int(11) NOT NULL,<br> `service_no` varchar(100) NOT NULL,<br> `servicename` varchar(50) NOT NULL,<br> `servicecategory` varchar(200) DEFAULT NULL,<br> `qty_per_unit` decimal(11,2) DEFAULT '0.00',<br> `unit_price` decimal(25,8) DEFAULT NULL,<br> `sales_start_date` date DEFAULT NULL,<br> `sales_end_date` date DEFAULT NULL,<br> `start_date` date DEFAULT NULL,<br> `expiry_date` date DEFAULT NULL,<br> `discontinued` int(1) NOT NULL DEFAULT '0',<br> `service_usageunit` varchar(200) DEFAULT NULL,<br> `website` varchar(100) DEFAULT NULL,<br> `taxclass` varchar(200) DEFAULT NULL,<br> `currency_id` int(19) NOT NULL DEFAULT '1',<br> `commissionrate` decimal(7,3) DEFAULT NULL,<br> PRIMARY KEY (`serviceid`),<br><b> CONSTRAINT `fk_1_vtiger_service` FOREIGN KEY (`serviceid`) REFERENCES `vtiger_crmentity` (`crmid`) ON DELETE CASCADE</b><br>) ENGINE=InnoDB DEFAULT CHARSET=utf8;<br><br></div>If you have to update an existing db<br><pre class="gmail-lang-sql gmail-prettyprint gmail-prettyprinted"><code><span class="gmail-kwd">ALTER</span><span class="gmail-pln"> </span><span class="gmail-kwd">TABLE</span><span class="gmail-pln"> users_role_map
</span><span class="gmail-kwd">ADD</span><span class="gmail-pln"> </span><span class="gmail-kwd">CONSTRAINT</span><span class="gmail-pln"> </span></code><code><span class="gmail-pln"><b>fk_1_vtiger_service</b></span><b><span class="gmail-pln"></span></b><span class="gmail-pln">
</span><span class="gmail-kwd">FOREIGN</span><span class="gmail-pln"> </span><span class="gmail-kwd">KEY</span><span class="gmail-pln"> </span><span class="gmail-pun">(serviceid</span><span class="gmail-pun">)</span><span class="gmail-pln"> </span><span class="gmail-kwd">REFERENCES</span><span class="gmail-pln"> </span></code><code><span class="gmail-pln"><b> `vtiger_crmentity` (`crmid`) </b></span><span class="gmail-pun"></span><span class="gmail-pln">
</span><b><span class="gmail-kwd">ON</span><span class="gmail-pln"> </span><span class="gmail-kwd">DELETE</span><span class="gmail-pln"> </span><span class="gmail-kwd">CASCADE<br><br><br></span></b></code></pre><pre class="gmail-lang-sql gmail-prettyprint gmail-prettyprinted"><code><b><span class="gmail-kwd">N</span></b><span class="gmail-kwd">eed to do this each table you need and replace "Service" module with required module.<br><br></span></code></pre><pre class="gmail-lang-sql gmail-prettyprint gmail-prettyprinted"><code><span class="gmail-kwd">Note you should have 'FORIEGN_KEY_CHECKS' Parameter in your MySQL server On (1) </span><b><span class="gmail-kwd"><br><br></span></b></code></pre><pre class="gmail-lang-sql gmail-prettyprint gmail-prettyprinted"><code><b><span class="gmail-kwd">Thanks<br>Sutharsan Jeganathan</span></b><span class="gmail-pun"></span></code></pre><br></div><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Oct 24, 2016 at 3:25 AM, socialboostdk <span dir="ltr"><<a href="mailto:socialboostdk@gmail.com" target="_blank">socialboostdk@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Hi there,<div><br></div><div>Thank you - ok, that sounds very good.</div><div><br></div><div>Do you have a list of Contstraints / Foreign keys - or perhaps even the required SQL to apply to the database?</div><div><br></div><div>Only relevant thing i could find after some searching was this: <a href="http://code.vtiger.com/alanbell/yetiforce/blob/de2eb4a7a5cd0a6e2ae91df905012dac7a65bc25/install/install_schema/scheme.sql" target="_blank">http://code.vtiger.com/<wbr>alanbell/yetiforce/blob/<wbr>de2eb4a7a5cd0a6e2ae91df905012d<wbr>ac7a65bc25/install/install_<wbr>schema/scheme.sql</a></div><div><br></div><div>Thanks for any input!</div><div><br></div><div>Best,</div><div>Kim</div></div><div class="gmail_extra"><br><div class="gmail_quote">On 23 October 2016 at 17:54, Sutharsan Jeganathan <span dir="ltr"><<a href="mailto:ajstharsan@gmail.com" target="_blank">ajstharsan@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div><div>Hi<br><br></div>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.<br><br><br></div>Thanks<br></div>Sutharsan Jeganathan<br></div><div class="gmail_extra"><br><div class="gmail_quote"><div><div class="m_8071907530393456929h5">On Fri, Oct 21, 2016 at 3:48 PM, socialboostdk <span dir="ltr"><<a href="mailto:socialboostdk@gmail.com" target="_blank">socialboostdk@gmail.com</a>></span> wrote:<br></div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div class="m_8071907530393456929h5"><div dir="ltr">Yes - i know its a known issue, unfortunately.<div><br></div><div>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... :)</div></div><div class="gmail_extra"><br><div class="gmail_quote">On 21 October 2016 at 11:51, cryptic <span dir="ltr"><<a href="mailto:mark@markcox.co.uk" target="_blank">mark@markcox.co.uk</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">This issue was raised quite a while back-:<br>
<br>
<a href="http://code.vtiger.com/vtiger/vtigercrm/issues/117" rel="noreferrer" target="_blank">http://code.vtiger.com/vtiger/<wbr>vtigercrm/issues/117</a><br>
<br>
<br><span class="m_8071907530393456929m_1916768488301810834HOEnZb"><font color="#888888">
<br>
--<br>
View this message in context: <a href="http://vtiger-crm.2324883.n4.nabble.com/Vtigercrm-developers-Deleting-records-from-Vtiger-modules-tables-Question-regarding-constraints-Fores-tp20011p20012.html" rel="noreferrer" target="_blank">http://vtiger-crm.2324883.n4.n<wbr>abble.com/Vtigercrm-developers<wbr>-Deleting-records-from-Vtiger-<wbr>modules-tables-Question-regard<wbr>ing-constraints-Fores-tp20011p<wbr>20012.html</a><br>
Sent from the vtigercrm-developers mailing list archive at Nabble.com.<br>
______________________________<wbr>_________________<br>
<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a><br>
</font></span></blockquote></div><br></div>
<br></div></div>______________________________<wbr>_________________<br>
<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a><br></blockquote></div><br></div>
<br>______________________________<wbr>_________________<br>
<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a><br></blockquote></div><br></div>
<br>______________________________<wbr>_________________<br>
<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a><br></blockquote></div><br></div>