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
Related Tags
foreign-key × 950mysql × 307
database-design × 219
postgresql × 190
sql-server × 178
primary-key × 118
constraint × 100
innodb × 43
referential-integrity × 43
oracle × 37
performance × 36
index × 36
delete × 32
join × 31
relational-theory × 30
mariadb × 25
normalization × 23
sqlite × 23
unique-constraint × 22
cascade × 22
insert × 20
trigger × 19
schema × 19
table × 19
sql-server-2008-r2 × 18