New answers tagged

8

As pointed out by Mr Brownstone (this would just be a comment but it turned out too long) the behaviour you are expecting can be achieved if you define the key as ON DELETE CASCADE. I would be very very careful with ON DELETE CASCADE, in fact I almost never use it and generally recommend against it. This counts for triggers that take action based upon rows ...


8

You need to specify that you want the delete action to cascade like so: CONSTRAINT `borclu_id_foreign` FOREIGN KEY (`borclu_id`) REFERENCES `borclular` (`id`) ON DELETE CASCADE If you do not specify either the ON DELETE or ON UPDATE then it defaults to RESTRICT, from the documentation: For an ON DELETE or ON UPDATE that is not specified, the default ...


2

Many variants have been posted, but the simplest check to find violating rows that have NULL in every column of the set is: SELECT * FROM integrations.accounts WHERE (qb_id,xero_id,freshbooks_id,myob_id,ppy_id) IS NULL; The WHERE expression evaluates to TRUE if and only if every single column IS NULL. Details: NOT NULL constraint over a set of columns ...


5

You can negate your constraint to find out the rows that does not satisfy it: SELECT * FROM integrations.accounts WHERE NOT ((("qb_settings" IS NOT NULL) or ("xero_settings" IS NOT NULL) or ("freshbooks_settings" IS NOT NULL) or ("myob_settings" IS NOT NULL) or ("ppy_settings" IS NOT NULL))) This can be ...


4

There are some row(s) that violate the constraint. But the 59 rows are not very relevant (although it includes the offending rows) because it's a different, bigger result set. It's the rows that have at least one of the 5 values as NULL. You want the ones that only have all 5 values as NULL. To find them, you can run the "opposite" of your constraint: ...


4

You have NULLs that violate the CHECK CONSTRAINT. To verify that this is indeed the problem, run this SQL. SELECT * FROM integrations.accounts WHERE qb_settings IS NOT NULL OR xero_settings IS NOT NULL OR... (fill in the fields that correspond to those in the CONSTRAINT). This will give you all the records that have fields which have a NULL ...


8

A variation on @a_horse_with_no_name's answer would be to first create the table with the constraint and then insert into the table, still using a transaction, so everything rolls back in case of constraint violations. This is something you should consider if the rows to be inserted are a lot (i.e. in the hundreds of thousands or millions). If it's ...


5

You need to do that with two statements: create table new_table as select ....; alter table new_table add constraint ...; Make sure you run this in a single transaction (e.g. by turning OFF auto commit in your SQL client, or wrapping that with a begin transaction ... If the alter table fails, you can rollback everything and the table is gone as well.



Top 50 recent answers are included