Setting up default values from another table

Question

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.

Answer 1

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

Answer 2

UPDATED ANSWER BASED ON CLARIFICATIONS IN QUESTION

I think because of the requirement that you can't use a constant default value for the state name, but must get the value from the States table, the solution would necessarily require triggers. So:

Create your tables like this:

CREATE TABLE States (
  name varchar(50) PRIMARY KEY,
  population int unsigned
); 

CREATE TABLE People (
  name varchar(100) PRIMARY KEY,
  state_name varchar(50),
  profession varchar(100)
); 

Then create the triggers - I'm relying on your comment that there is only ever one record in the States table, but this could be modified to use the first record or use some other condition if you want more recorrds in that table:

DELIMITER ;;
CREATE TRIGGER People_insert_before 
BEFORE INSERT ON People 
FOR EACH ROW
BEGIN
  DECLARE sname varchar(50);
  IF NEW.state_name IS NULL THEN
    SELECT `name` INTO sname FROM States LIMIT 1;
    SET NEW.state_name = sname; 
  END IF;
END;;

CREATE TRIGGER States_update_after 
AFTER UPDATE ON States 
FOR EACH ROW
BEGIN
  IF NEW.name <> OLD.name THEN
    UPDATE People SET state_name = NEW.name WHERE state_name = OLD.name;
  END IF;
END;;
DELIMITER ;

Then insert some data:

INSERT INTO States (`name`, population) VALUES ('Montana', 2000000);
INSERT INTO People (`name`, profession) VALUES ('John', 'Programmer');

Then get a list of people and their states:

SELECT name, state_name, profession 
FROM People p;

This gives:

+------+------------+------------+
| name | state_name | profession |
+------+------------+------------+
| John | Montana    | Programmer |
+------+------------+------------+

Then you can update the "default" state. (If you use DELETE/INSERT, then you need a different trigger):

UPDATE States SET `name` = 'Alabama' WHERE `name` = 'Montana';

And if you then re-run the SELECT query, you get:

+------+------------+------------+
| name | state_name | profession |
+------+------------+------------+
| John | Alabama    | Programmer |
+------+------------+------------+

Answer 3

Much of this answer doesn't correspond to the OPs request - but it might be useful for those wishing to learn about MySQL's MODIFY/CHANGE COLUMN syntax (as opposed to the ALTER COLUMN standard syntax). The section that (IMHO) most closely corresponds to what the OP wanted starts at the part following EDIT.

If I've understood your question correctly, you need to use the MODIFY COLUMN DDL syntax in conjunction with a FOREIGN KEY (to add one of these, the ALTER option does work - see second dbfiddle below!).

Unlike most servers, for modifying DEFAULTs, MySQL doesn't use the standard ALTER COLUMN syntax, but rather the alternative of either MODIFY or CHANGE COLUMN - see below. MySQL uses many non-standard forms!

To solve your initial problem, I did the following (see dbfiddle-1):

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


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

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

Then, I did this:

INSERT INTO person (person_name, profession) VALUES  ('Fred', 'Programmer');  
            -- note, no person_state data for the INSERT - i.e. it's NULL

SELECT * FROM person;  

And the result of this SELECT is

person_name     person_state    profession
       Fred          Alabama    Programmer

As you'd expect, since the default for a NULL person_state INSERT is 'Alabama'. However, this won't work if there's a FOREIGN KEY constraint and the state table doesn't contain 'Alabama' as a state_name! See the second dbfiddle below.

Then comes the tricky bit (at least for those of us used to standard syntax)

You can use this form:

ALTER TABLE person MODIFY COLUMN person_state VARCHAR(20) DEFAULT 'New York';

(you could also use this (bizarre!) syntax)

ALTER TABLE person CHANGE COLUMN person_state person_state VARCHAR(20) DEFAULT 'New York';  
            -- yes, you **do** have to repeat the column name!  

Then run:

INSERT INTO person (person_name, profession) VALUES  ('Jimmy', 'DBA');
                    -- again NULL for person_state
SELECT * FROM person;  

Result:

person_name     person_state    profession
       Fred          Alabama    Programmer
      Jimmy         New York           DBA

So, now, the NULL insert becomes 'New York' as specified in the new DEFAULT clause. I found this post helpful.

Following your edit, I added to the previous (see dbfiddle-2) to further clarify:

INSERT INTO state VALUES ('New York', 20000000);  
INSERT INTO state VALUES ('Alabama',   3000000);

And also

ALTER TABLE person ADD CONSTRAINT person_state_fk FOREIGN KEY (person_state) REFERENCES state (state_name);  

The way to look at a FOREIGN KEY constraint is that it is an expanded form of the DEFAULT clause - it says that you can take a default, but that it must come from a particular field (or fields!) in a given table!

I hope this clarifies matters, if not, let me know!

[EDIT - following a discussion in comments]

You could also look at the concept of a DOMAIN - it's a user defined datatype that can be used throughout the database.

From the PostgreSQL documentation here, "CREATE DOMAIN creates a new domain. A domain is essentially a data type with optional constraints (restrictions on the allowed set of values).`"

From the FirebirdSQL documentation here, "Domain is one of the object types in a relational database. A domain is created as a specific data type with some attributes attached to it. Once it has been defined in the database, it can be reused repeatedly to define table columns"

All of the below DDL and DML is in the fiddle here - AFAICS, this is the closest concept in the SQL standard to what you want - it allows you to specify values but has prescriptions on allowed values.

DDL: (this works for PostgreSQL 10 - not for MySQL which doesn't have the DOMAIN keyword - I don't have a Firebird system to test against!

CREATE DOMAIN D_STATE_NAME AS VARCHAR(40) DEFAULT 'New York' NOT NULL;

CREATE TABLE state (state_name D_STATE_NAME PRIMARY KEY, population INTEGER);

CREATE TABLE person (person_name VARCHAR(50), person_state D_STATE_NAME, profession VARCHAR(50));

-- you can experiment with commenting out the ALTER TABLE statement


-- ALTER TABLE person ADD CONSTRAINT person_state_fk FOREIGN KEY (person_state) REFERENCES state (state_name);

INSERT INTO state VALUES ('Montana',     2000000);
INSERT INTO state VALUES ('Alabama',     3000000);
INSERT INTO state VALUES ('New York',   30000000);
-- INSERT INTO state VALUES ('California', 50000000);  -- if the FK isn't present, you can't put 'California' as a state, but if the FK is there, you can't!  

DML:

INSERT INTO person (person_name, profession) VALUES  ('Fred', 'Programmer');

INSERT INTO person (person_name, person_state, profession)
VALUES ('Jimmy', 'Montana', 'DBA');

SELECT * FROM person;

-- INSERT INTO person VALUES ('Mary', NULL, 'CIO');  -- not allowed - breaches NOT NULL CONSTRAINT

INSERT INTO person (person_name, profession) 
VALUES ('Mary', 'CIO'); -- works because of DEFAULT 'New York' clause - maybe a bug in PostgreSQL? If this works, then the above statement should work - i.e. the `DEFAULT` clause should kick in!

SELECT * FROM person;

INSERT INTO person VALUES ('Paulie', 'California', 'Janitor');

SELECT * FROM person;

I hope this may (at least partially) correspond to your requirements while not being in SQL, and to change it, you can drop the DOMAIN and change it to whatever state you now wish to be the default!