How to edit restriction on rows that are already defined and filled in mysql-CodePudding

I have a table name temp and in it I have column x with datatype INT.

mysql> desc temp;
 ------- ------ ------ ----- --------- ------- 
| Field | Type | Null | Key | Default | Extra |
 ------- ------ ------ ----- --------- ------- 
| x     | int  | YES  |     | NULL    |       |
 ------- ------ ------ ----- --------- -------

As shown below I have inserted value in the column.

mysql> select * from temp;
 ------ 
| x    |
 ------ 
|   10 |
 ------

Is it possible to set restriction on already defined column x such that it only takes integers with value less than 10.

Note: I do not intend to create a new column but rather add restriction on already created column

CodePudding user response：

Is it possible to set restriction on already defined column x such that it only takes integers with value less than 10.

Yes. In my opinion you could create a trigger to set restriction (x < 10 in your case). This worked for me:

CREATE TRIGGER check_trigger
  BEFORE INSERT
  ON tmp
  FOR EACH ROW
BEGIN
  IF NEW.x >= 10 THEN
    CALL `Error: Wrong values for field x. x must be less than 10`; -- this trick will throw an error
  END IF;
END ;

With this trigger I got error when trying to create a record with x = 11 image for more details You may want to create one more trigger for the update

CodePudding user response：

Yes you can add constraint to a already defined and filled column. As @Akina has pointed out in comments that for adding restriction as CHECK constraint you need to update previously filled data to match the new constraint. This is done to ensure that the newly applied constraint do not fail. After updating the data you can apply the CHECK constraint as follows:

ALTER TABLE temp
ADD CHECK (x <= 10);