I've got a simple question: I was wondering if it is possible to (temporary or permanent if possible) block the insertion of a record into a table with a specific value for a specific column. I'm using mysql with InnoDB

So I have this table Cart, which contains some columns and one of them is CartID (it is not the primary key thou). And I would like to block insertions in the table Cart with a specific CartID (let's say 100), while I'm doing work on another table in the DB.

  • By block, do you mean that once you are done with the work on another table in the DB the transactions that queued can then flow through or you want to kill those transactions completely? – Shaulinator May 8 '18 at 15:43
  • Add a constraint to the column perhaps? – Jacob H May 8 '18 at 15:43
  • Hello OP here, yes I would like to kill it completely (i know block is not the correct word since it is used in DB world as something different). Basically when I try to add something with the ID 100, it should reject the insertion. – Bosiwow May 8 '18 at 15:48

The most powerful approach is to wrap all the basic operations like INSERT, DELETE, UPDATE and SELECT into the set of the stored routines. That acts like an API to the database and can be extended by any desired checkouts/conversions/calculations you ever want. If parameters passed to the stored routine meet some conditions you can INSERT them. Otherwise you reject those data without inserting.

Same effect can be achieved by trigger BEFORE INSERT that look like this:

  IF NEW.id IN (100, 200, 300, 456)
    THEN SET NEW.id = 1;

where id=1 is already existent record and id column have UNIQUE constraint. But triggers in general are unclear and non-transparent tool that can be very difficult to debug. Also autoincrements and triggers can interfere with unforeseen consequencies so it's better to avoid triggers as possible.

  • But can I dynamically update those stored routines? Because if I decide in my program suddenly that cartID should temporarily not be allowed, can I force it? – Bosiwow May 8 '18 at 18:22
  • @Bosiwow Yes, SRs can be updated on the fly. Even better, SR can fetch blocked IDs from the separate table, that can be managed with plain inserts/deletes. – Kondybas May 8 '18 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.