MySQL alter table change column throw error if rows change

Question

This question might probably have been asked somewhere but I just can't seem to find it after much searching.

I have a basic question on how I can prevent the last SQL statement in my code below from executing if it caused any row/data in the table to change.

$query = 'SELECT pdt_code FROM `Products`';
$stmt = $con->query($query);
while ($obj = $stmt->fetch()) {
    $pdt_code[] = $obj['pdt_code'];
}

$args = implode(',', 
            array_map(function($el) { 
                return '('.implode(',', $el).')'; 
            },
            array_chunk(array_fill(0, count($pdt_code), '?'), count($pdt_code))
            )
        );

$query = 'ALTER TABLE `MapProduct` CHANGE product_code product_code SET '.$args.' COLLATE utf8mb4_unicode_ci NOT NULL';
$stmt = $con->prepare($query);
$stmt->execute($pdt_code); // how do I prevent this line from executing if it causes the data in table `MapProduct` to change?

I am trying to execute this from PHP where I query another table for updates to product codes and update the SET() accordingly. However, I had a horrific experience of losing all my data because the update was not properly executed and all the data in product_code got wiped.

@davidstrachan Because the table where products are stored will be modified from time to time. And if the SET fails to be updated accordingly, any new inserts will be rejected. — revvy
– revvy, Commented May 23, 2016 at 13:50
You should not use set in this case. Use a lookup table and a foreign key to restrict the list product codes. In that case all you need to do is to insert a new record into your product codes lookup table. — Shadow
– Shadow, Commented May 23, 2016 at 15:03

Shadow · Accepted Answer · 2016-05-23 15:07:18Z

1

Although mysql's SET data type can be used to restrict list of values being entered. However, it is not flexible and requires an alter table to update the list of values. If you frequently have to do this - and if you build a functionality to do that, then you need to do this frequently - then the SET data type is simply not for you.

I would create a separate table for product types and I would simply reference this table with a foreign key from your mapproduct table. This way adding a new product type to the allowed list inly requires an insert statement.

answered May 23, 2016 at 15:07

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL alter table change column throw error if rows change

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related