3

When you declare a foreign key, the column named must be a column in the table that contains the foreign key. FOREIGN KEY (branchNo) REFERENCES Branch In this case, branchNo must be a column in the Staff table. But there is no column by that name in the Staff table you showed. Next, MySQL requires you to name the column in the referenced table as well. ...


2

You can indeed do this. If you scroll lower down on the linked postgres documentation, you'll see: Sometimes it is useful for the “other table” of a foreign key constraint to be the same table; this is called a self-referential foreign key. For example, if you want rows of a table to represent nodes of a tree structure, you could write: CREATE TABLE tree ( ...


1

This query shows all table and column names that reference column id in table data: SELECT fk.conrelid::regclass AS referencing_table, a2.attname AS referencing_column FROM pg_constraint AS fk JOIN pg_constraint AS uk ON fk.conindid = uk.conindid CROSS JOIN LATERAL unnest(uk.conkey) WITH ORDINALITY AS ukc(attnum, n) JOIN LATERAL unnest(fk....


1

No, taking multiple locks is not atomic. Yes you can get a deadlock. It is easy to demonstrate this by reversing steps 1 and 2. With the steps in the order you give, it would be hard to get the timing exactly right to hit it. But it is the same principle so it must also be possible.


1

I have no explanation for that pg_cron behavior — those two DELETE statements should be able to run in parallel just fine. Try to run the two deletes by hand in two database sessions and see if you can reproduce the behavior. Make sure that the foreign key column is indexed on both child tables, otherwise deleting from the parent will be very slow (which ...


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