Yeongchan Jeon Yeongchan Jeon - 1 month ago 4
MySQL Question

Can I make two attributes of a MySQL table always have the same value?

This is the command I put in:

create table inventory(
idx int primary key,
name varchar(20),
location varchar(20),
quantity int);


In this table, I want attributes
name
and
location
to share the same value at all times. Can I do this in a table creation statement? Or do I have to set something after creating it?

Answer

You can set default values for those two fields in the create table statement.

CREATE TABLE inventory (
    idx INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR (20) NOT NULL DEFAULT 'DEFAULT VALUE SET',
    location VARCHAR (20) NOT NULL DEFAULT 'DEFAULT VALUE SET',
    quantity INT
);

If you don't provide any values for those two fields while inserting then the default value will persist.

EDIT:

Through before update on trigger you can accomplish this.

DROP TRIGGER IF EXISTS `inventory_trigger`;


DELIMITER //
    CREATE TRIGGER inventory_trigger BEFORE UPDATE ON inventory FOR EACH ROW
  BEGIN 
        IF (NEW.name <> OLD.name ) THEN
            SET NEW.location = NEW.name ;
        END IF;
        IF (NEW.location <> OLD.location ) THEN
            SET NEW.name = NEW.location ;
        END IF;
  END//

If the location field changes then this change will also reflect in the name column and vice versa.

Comments