1

I am need to create a database that contains a table Member, who have four attributes: first, middle, last name, and name.

Is it possible to indicate that name should be set to First + Middle + Last while using the 'CREATE TABLE member' or does this have to occur after creating the table?

Example:

CREATE TABLE member (first_name varchar(80) NOT NULL, middle_name varchar(80) NULL, last_name varchar(80) NOT NULL, name varchar(255) NOT NULL);

usernameabc
  • 662
  • 1
  • 10
  • 30
  • 2
    Can't you just avoid the name field altogether? You introduce unnecessary redundancy. This just begs for inconsistencies. My suggestion would be: ditch the name field and when you need a full name, just generate it on the fly from the other fields. – mmgross Feb 26 '15 at 01:41
  • See http://stackoverflow.com/a/7477401/1594449 and http://stackoverflow.com/a/5422798/1594449 – gknicker Feb 26 '15 at 01:53
  • possible duplicate of [how to create calculated field in mysql?](http://stackoverflow.com/questions/4236912/how-to-create-calculated-field-in-mysql) – gknicker Feb 26 '15 at 02:28

1 Answers1

1

You could create a BEFORE INSERT trigger and a BEFORE UPDATE trigger to set the name field to the value of CONCAT_WS(' ', first_name, middle_name, last_name) as follows... but don't do that. It's a terrible idea. Don't store the name column at all. When you want to select the name, just select CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name.

Note that CONCAT will return null if any of the values you're concatting is null so you probably want to use CONCAT_WS (with separator) instead - if any value in the list is null it will just omit that value and use the remaining ones.

Your triggers might look something like this if you decided to do that:

 CREATE TRIGGER name_update BEFORE UPDATE ON member
    FOR EACH ROW
    BEGIN
        SET NEW.name = CONCAT_WS(' ', NEW.first_name, NEW.middle_name, NEW.last_name);
    END;

 CREATE TRIGGER name_insert BEFORE INSERT ON member
    FOR EACH ROW
    BEGIN
        SET NEW.name = CONCAT_WS(' ', NEW.first_name, NEW.middle_name, NEW.last_name);
    END;

I hope that helps.

ratsbane
  • 890
  • 6
  • 7
  • it's not a terrible idea now we know the nosql databases; we can retrieve de data fast, and it's useful for a lot of operations – stackdave Nov 09 '17 at 09:53