Jump to content

MySQL error cannot add foreign key constraint

sjaakie3

By sjaakie3
in Core developers

 Share

Recommended Posts

sjaakie3 Newbie

sjaakie3

Posted
Posted

When will the table definitions be updated and include foreign key constraints?

I know this has been asked before, many years now, why is this not picked up by the developers?

 

Proper database design nowadays suggests/states that foreign keys constraints are a must.

  • Like 1
Link to comment
Share on other sites
sjaakie3 Newbie

sjaakie3

Posted
  • Author
Posted (edited)

Most people will be using INODB on Mysql, it's the default nowadays.

 

Starting from MySQL 5.5.5, the default storage engine for new tables is InnoDB . This change applies to newly created tables that don't specify a storage engine ...

 

 

http://dev.mysql.com/doc/refman/5.7/en/innodb-default-se.html

 

Maybe it is time to start supporting it, it has a lot of advantages. I tried to do it myself, but there are so many badly written sql statements in Prestashop that it will not work right now. 

 

This is not the first discussion of this kind, more people have been wondering why not to support it, no valid reasons were given. You can scan this thread: http://stackoverflow.com/questions/83147/whats-wrong-with-foreign-keys

 

Maybe my favorite quote from the thread:

 

 

Most everyone was taught when they were kids that brushing your teeth was important. Can you get by without it? Sure, but somewhere down the line you'll have less teeth available than you could have if you had brushed after every meal. If moms and dads were responsible enough to cover database design as well as oral hygiene, we wouldn't be having this conversation. :-)
Edited by sjaakie3 (see edit history)
Link to comment
Share on other sites
sjaakie3 Newbie

sjaakie3

Posted
  • Author
Posted

Sjaakie, did you also consider the effects this may have on people who will upgrade their shop to the newest version?

Could be a new version, Prestashop 2.0

 

And btw, backwards compatibility and thorough testing is not something that Prestashop is famous for.

Link to comment
Share on other sites
bellini13 Newbie

bellini13

Posted
Posted

Maybe it is time to start supporting it, it has a lot of advantages.

what specific advantages would Prestashop get by using FK's in InnoDB?  There are not even any advantages to using InnoDB, as Prestashop does not use transactions that can be committed or rolled back.

 

I'm sure if you can document what these advantages and benefits would be, and then create an enhancement request in their Forge, Prestashop would consider it.

Link to comment
Share on other sites
sjaakie3 Newbie

sjaakie3

Posted
  • Author
Posted

what specific advantages would Prestashop get by using FK's in InnoDB?  There are not even any advantages to using InnoDB, as Prestashop does not use transactions that can be committed or rolled back.

 

I'm sure if you can document what these advantages and benefits would be, and then create an enhancement request in their Forge, Prestashop would consider it.

What is wrong with you people, are you so scared of progress?

All I ask if PS will be brought a little bit forward. Php and databases technolgies are not stagnant, PS development should not be either.

Everything flows and progresses.

 

But I can imagine there will have to be a lot of work done before PS in its present state can be moved forward technically. 

I myself used to use CakePHP a lot, now I switched over to YII2 because.. well you know everything is fluid. 

Link to comment
Share on other sites
bellini13 Newbie

bellini13

Posted
Posted

There is nothing wrong with 'us people', I certainly am not afraid of change, however I choose not to change unless there is some benefit, and you have not demonstrated any benefit.

 

Now you wanted to ask PS to enhance their software, and I provided you with the steps necessary to do it.  Your posts and suggestions are not being seen by anyone who would make those decisions. 

 

If you would like to continue to argue about it, that is fine too, but your arguments are falling on deaf ears.

Link to comment
Share on other sites
  • 4 months later...
jorge911 Newbie

jorge911

Posted
Posted (edited)

I'm another developer asking myself how is it possible that there are no foreign keys defined in prestashop.

 

Above it is asked what are the benefits for PS on using FK. There are many academic papers explaining it but there is a good resume on stackoverflow: http://stackoverflow.com/questions/83147/whats-wrong-with-foreign-keys

 

Here is the list with some of my additional comments,

 

Reasons to use Foreign Keys:

  • you won't get Orphaned Rows (real benefit for PS!)
  • you can get nice "on delete cascade" behavior, automatically cleaning up tables (real benefit for PS!)
  • knowing about the relationships between tables in the database helps the Optimizer plan your queries for most efficient execution, since it is able to get better estimates on join cardinality. (real benefit for PS!!)
  • FKs give a pretty big hint on what statistics are most important to collect on the database, which in turn leads to better performance (real benefit for PS!!)
  • they enable all kinds of auto-generated support -- ORMs can generate themselves, visualization tools will be able to create nice schema layouts for you, etc (will help PS developers)
  • someone new to the project will get into the flow of things faster since otherwise implicit relationships are explicitly documented (will help PS developers)

Reasons not to use Foreign Keys:

  • you are making the DB work extra on every CRUD operation because it has to check FK consistency. This can be a big cost if you have a lot of churn (on PS it will probably be offset by the performance improvements above, it would need to be tested)
  • by enforcing relationships, FKs specify an order in which you have to add/delete things, which can lead to refusal by the DB to do what you want. -Granted, in such cases, what you are trying to do is create an Orphaned Row, and that's not usually a good thing-. This is especially painful when you are doing large batch updates, and you load up one table before another, with the second table creating consistent state -but should you be doing that sort of thing if there is a possibility that the second load fails and your database is now inconsistent?-. (since PS does not use batch transactions the pain is aliviated and by enforcing relationships and having the DB refuse creating orphaned rows by the code is actually an improvement on database integrity and performance, although initially a bit of a pain for core developers who will start to create SQL queries that satisfy the correct database relationships).
  • sometimes you know beforehand your data is going to be dirty, you accept that, and you want the DB to accept it (is that what PS developers have done? But it shouldn't be dirty for the benefit of everyone!!!).
  • you are just being lazy :-) (same question as on previous point :-) )

Real, business, serious software developement uses foreign keys on its DBs. I know because I have migrated databases at multinational telecomunication companies and we had to keep databases foreign keys (well, for the migration we had to drop them all and then recreate...). So if Prestashop wants to become a real ecommerce leader it should enforce foreign keys... in my opinion.

 

Or do we create a comunity branch and we start to enforce it?

Once enforced in the DB and corrected the relevant code, keeping in sync with the main official branch should be quite easy, just review and correct if necessary any additional queries found.

Edited by jorge911 (see edit history)
Link to comment
Share on other sites
sjaakie3 Newbie

sjaakie3

Posted
  • Author
Posted

falling on deaf ears...

exactly! 

 

One of the reasons I switched from CakePHP to YII 2.0 for all my projects. Really strange stubborn attitude from Prestashop. Must be because they never have to do with real-world projects.

 

Let's fork it and make it better!

Link to comment
Share on other sites
jorge911 Newbie

jorge911

Posted
Posted (edited)

I know the meaning, the doubt was if it was falling on his deaf ears or on the deaf ears of the core developers...

 

switch ($on_whose_ears_it_falls) {

  case "on his ears":

       $my_answer = "I don't think it is a very constructive answer (although I might be wrong)";

       break;

  case "on core developers ears":

      $my_answer = "it would help to know why he thinks the core developers will be deaf to it";

      break;

  default:

      $my_answer = "then, on whose ears?";

}

 

$this->context->smarty->assign(array(

            'answer' => $my_answer,

           ));

 

you can work out the tpl ;)

 

(no pun intended, just a little bit of humor on a Sunday ;) )

Edited by jorge911 (see edit history)
Link to comment
Share on other sites
bellini13 Newbie

bellini13

Posted
Posted

as i had already said in my previous posts, the people you want to read and understand your opinion do not actually read these threads. 

 

if you really want these features considered, you need to open a forge ticket with your enhancement request.  otherwise your feedback is "falling on deaf ears"

Link to comment
Share on other sites
jorge911 Newbie

jorge911

Posted
Posted (edited)

as i had already said in my previous posts, the people you want to read and understand your opinion do not actually read these threads. 

 

if you really want these features considered, you need to open a forge ticket with your enhancement request.  otherwise your feedback is "falling on deaf ears"

Ok, I understand now... There is a forge ticket already for this: http://forge.prestashop.com/browse/PSCFV-7286

and I have added my comments.

 

Unfortunately there is this entry on the history:

 
Damien Metzger changed the Priority to 'Minor' on PSCFV-7286 - There is lack of foreign keys in database improvement.png22/Aug/13 11:12 AM

 

Which is discouraging, but if we keep adding comments to that ticket may be they will open their ears... or we will have to organise a comunity team to fork and take on the job. It's not serious not having foreign keys, it is like driving a car without a seatbealt!

 
 
Edited by jorge911 (see edit history)
Link to comment
Share on other sites
  • 1 year later...
lxhost Newbie

lxhost

Posted
Posted 
if (_MYSQL_ENGINE_ == 'InnoDB') {
    $result1 = Db::getInstance()->getRow("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` WHERE `TABLE_NAME` = '"._DB_PREFIX_."lr_bitcoin_address' AND `CONSTRAINT_NAME` = '"._DB_PREFIX_."lr_bitcoin_address_ibfk_1' AND `TABLE_SCHEMA` = '"._DB_NAME_."'");
    if (!$result1) {
        Db::getInstance()->execute("ALTER IGNORE TABLE `"._DB_PREFIX_."lr_bitcoin_address` ADD CONSTRAINT `"._DB_PREFIX_."lr_bitcoin_address_ibfk_1` FOREIGN KEY (`id_order`) REFERENCES `"._DB_PREFIX_."orders` (`id_order`) ON DELETE CASCADE ON UPDATE CASCADE");
    }

    $result2 = Db::getInstance()->getRow("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` WHERE `TABLE_NAME` = '"._DB_PREFIX_."lr_bitcoin_transaction' AND `CONSTRAINT_NAME` = '"._DB_PREFIX_."lr_bitcoin_transaction_ibfk_1' AND `TABLE_SCHEMA` = '"._DB_NAME_."'");
    if (!$result2) {
        Db::getInstance()->execute("ALTER IGNORE TABLE `"._DB_PREFIX_."lr_bitcoin_transaction` ADD CONSTRAINT `"._DB_PREFIX_."lr_bitcoin_transaction_ibfk_1` FOREIGN KEY (`address`) REFERENCES `"._DB_PREFIX_."lr_bitcoin_address` (`address`) ON DELETE CASCADE ON UPDATE CASCADE");
    }
}

This is an example from one install.php file how to use foreign key in a database with InnoDB engine.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...