3 votes

the REFERENCES privilege is only about creating a foreign key constraint? Practical use cases?

To extend on Erwin's answer, there's a third case why the creation of foreign key constraints may require special permissions: a foreign key grants users of the table the ability to test for ...
  • 505
1 vote

Is it possible to insert cyclic foreign keys that are AUTO INCREMENT primary keys?

Cyclic FK constraints are always a problem. One way to deal with this, is to fetch the "next ID" manually using nextval(). In order to not hardcode the underlying sequence names, ...
1 vote

Cascading foreign key DELETE with tables that have RESTRICT constraints

Using triggers would be a bit messy You should do the cascade delete manually Perhaps something like this SET FOREIGN_KEY_CHECKS = 0; START TRANSACTION; SELECT indx INTO @indx_to_cascade FROM orgs ...
1 vote

Deferrable Foreign Key Constraints

Deferrable constraints can prevent optimizer transformations. Here's a simple example validating percentages must be between zero and one hundred: create table exam_results ( student_id integer, ...
  • 2,471

Only top scored, non community-wiki answers of a minimum length are eligible