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, ...
- 77.2k
1
vote
Accepted
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 ...
- 178k
1
vote
Accepted
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