You can solve this by allowing NULL in the foreign key column tblImageFlags.resolutionTypeID. PS Bonus points to whomever tells me whether, in the case of databases, it’s “indexes” or “indices”. The plural of index should be indexes. According to “Modern American Usage” by Bryan A. Garner: For ordinary purposes, indexes is the preferable plural, not … Read more
Looks like my answer is here… Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; … Read more
In PostgreSQL 9.1 or later you can do this with a single statement using a data-modifying CTE. This is generally less error prone. It minimizes the time frame between the two DELETEs in which a race conditions could lead to surprising results with concurrent operations: WITH del_child AS ( DELETE FROM child WHERE child_id = … Read more
CHECK constraints cannot currently reference other tables. The manual: Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row. One way is to use a trigger like demonstrated by @Wolph. A clean solution without triggers: add redundant columns and include them in FOREIGN KEY constraints, which are the … Read more
Reasons to use Foreign Keys: you won’t get Orphaned Rows you can get nice “on delete cascade” behavior, automatically cleaning up tables 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. FKs give a … Read more
Create two partial indexes: CREATE UNIQUE INDEX favo_3col_uni_idx ON favorites (user_id, menu_id, recipe_id) WHERE menu_id IS NOT NULL; CREATE UNIQUE INDEX favo_2col_uni_idx ON favorites (user_id, recipe_id) WHERE menu_id IS NULL; This way, there can only be one combination of (user_id, recipe_id) where menu_id IS NULL, effectively implementing the desired constraint. Possible drawbacks: You cannot have … Read more