Mysql – Setting up default values from another table

MySQL

Here is a problem I am trying to solve. Suppose I have a table called States (Name, Population). Let us assume it has one row – (Montana, 2million). Now suppose I have another table called People (Name, State, Profession). In this table I would like to set a default for state as the column value Name of table States, i.e., Montana. Now, doing so, for a tuple in People, (John, NULL, Programmer), I should get (John, Montana, Programmer) when I select this row. Suppose at a later date, I update Montana to Alabama. I should get (John, Alabama, Programmer). Any idea how using default, constraints, trigger or anything like that.

A bit more clarification:

Suppose I did

CREATE TABLE state 
  ( state_name VARCHAR(20), 
    population INTEGER
  );

INSERT INTO state VALUES ('Montana', 2000000);

Then I did

CREATE TABLE person 
  ( person_name VARCHAR(50),
    person_state VARCHAR(20) DEFAULT sate_name(state), 
    profession VARCHAR(50)
  );

INSERT INTO person VALUES (NULL, 12000);
INSERT INTO person VALUES ('New York', 145000);
INSERT INTO person VALUES ('Mississippi', 5000);
INSERT INTO person VALUES (NULL, 1000);

I know, I cannot do the DEFAULT as I wrote. But assuming I can do something that will let me achieve the effect is what I am looking for.

After this, if I wrote

SELECT *
FROM person

Should give me

Montana, 12000
New York', 145000
Mississippi', 5000
Montana, 1000

Now if I deleted the tuple ('Montana', 2000000) and then inserted ('Alabama', 50000), the query

SELECT *
FROM person

It should give me

Alabama, 12000
New York', 145000
Mississippi', 5000
Alabama, 1000

Hope it explains. I know about default, and join that you all suggested. Thanks for those comments. But that is not what I am hoping to achieve.

The MOST important thing to observe here is that the default value should come from another table, and cannot be a constant. For example, it cannot be the following statement:

CREATE TABLE person (person_name VARCHAR(50), person_state VARCHAR(20) DEFAULT 'Alabama', profession VARCHAR(50));

Where 'Alabama' is a constant. It must be a column name from another table.

Best Answer

I think you are making this much more complicated than needed. If I understand the requirements, you want a single state, stored in the state table to cat as the default state value for all persons. And if that default state is changed, the change should be reflected in all - existing and future - rows of person.

If the above interpretation is correct, then you don't need to store a default in persons (and then update it every time the default changes). Store NULL instead! This is how it will work:

CREATE TABLE state 
      ( state_name VARCHAR(20), 
        population INTEGER
      );

INSERT INTO state VALUES ('Montana', 2000000);

CREATE TABLE person 
      ( person_name VARCHAR(50),
        person_state VARCHAR(20) DEFAULT NULL,        -- yes! 
        salary INTEGER
      );

INSERT INTO person 
VALUES 
  ('Alex', NULL, 12000),
  ('Bill', 'New York', 145000),
  ('Cath', 'Mississippi', 5000),
  ('Dean', NULL, 1000) ;

Then we use COALESCE() to get the default value from state if person_state is null:

    SELECT p.person_name,
           COALESCE(p.person_state, s.state_name) AS person_state,
           p.salary
    FROM person AS p
      CROSS JOIN state AS s ;

person_name | person_state | salary
:---------- | :----------- | -----:
Alex        | Montana      |  12000
Bill        | New York     | 145000
Cath        | Mississippi  |   5000
Dean        | Montana      |   1000

Change the default state:

DELETE FROM state;

INSERT INTO state VALUES ('Alabama', 50000);

And see the results:

    SELECT p.person_name,
           COALESCE(p.person_state, s.state_name) AS person_state,
           p.salary
    FROM person AS p
      CROSS JOIN state AS s ;

person_name | person_state | salary
:---------- | :----------- | -----:
Alex        | Alabama      |  12000
Bill        | New York     | 145000
Cath        | Mississippi  |   5000
Dean        | Alabama      |   1000

dbfiddle here

Related Question