Tag Info

New answers tagged

-2

Deferrable constraints are usually wrong. It's a proof that your ORM does something wrong. Usual problems are commit might fail. This is usally not expexted by various tools and also by developers commit might last very long. This might be problem with distributed(XA) transactions. also troubleshooting might be problematic, especially when using ...


4

You need to remove the status column from your index: CREATE UNIQUE INDEX builders_unique_house ON houses (builder_id) WHERE status IN ('PLANNING', 'CONSTRUCTING'); If you keep the status column, the index will contain e.g. ('PLANNING', 1) and ('CONSTRUCTING', 1) - that combination is unique. If you remove the status, only the builder_id will be ...


0

First, sorry if I have a bad English. Even if you dropped the constraints from the table you are trying to delete, the constraints of any children tables will affect the performance of the parent table. This occurs because the database needs to do an select on all the foreing keys pointed to the main table each line of delete to guarantee the integrity of ...


0

Here's what I eventually came up with. Of course, I'm open to input/refinements from the DBA grandfathers around here! But for now, this approach is giving me the info I need. USE INFORMATION_SCHEMA; Get all tables that have at least one foreign key referencing a column in "mytable" SELECT TABLE_NAME, COUNT(*) AS reference_count FROM ...


0

Foreign key can be multicolumn, so having single value like "REFERENCED_COLUMN" is not possible. Instead the table seems to refer to index covering the referenced column(s), you could try to check these. But there seems to be better possibility with information_schema.KEY_COLUMN_USAGE, which lists directly table/column name and for it referenced ...



Top 50 recent answers are included