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.

• ypercubeᵀᴹ: Will this state table always have 1 row?
• DEarTh: Please add expected result for more than one row.
• DEarTh: select p.person_name,s.state_name, p.profession from person p join state s; is that what you want ?
• Hasan: Yes, one row. ypercube.
• Hasan: Jitendra, now I have several rows.

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

• Hasan: ypercube, yes that is what I do now. Thanks. I though used case. But I am trying to use database constraints systems to do what I want to do. I am actually trying to design a new SQL system that will help me do this easily. That requires query rewriting. I do not want to rewrite such complex queries. Instead, I would like to pose usual queries, and rewrite constraints. Thats what I am after. Thanks.
• ypercubeᵀᴹ: I don't see how this can lead to complex query rewrites. You could just define the query I provided as a view. It essentially takes all the columns from person except the state which uses either person or the deafult state.
• Hasan: Because I want to simply write SELECT * FROM person or this SELECT person_state, p.salary FROM person and not 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 ; Someone needs to make this rewrite.
• ypercubeᵀᴹ: That's what I said. You only define this once, as a view. Then everyone uses SELECT * FROM person_v ;
• Hasan: Yes, defining this as a view will work. But, the query should then be on the view, and view will invite view maintenance problems which I did not want to invite. But yes, as I said before, I can even live with the rewrite, which I am currently doing with case statement, which I write for every query submitted. I just wanted to see if the constraint system can be used in some fashion, and if anyone did so already.

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 |
+------+------------+------------+

• Hasan: Thanks for those comments. But that is not what I am hoping to achieve. See the updated question.
• dbdemon: @Hasan I've re-written the answer based on your clarifications in the updated question. It looks like triggers is the only way to fulfil all your requirements.
• Hasan: I think I like your thinking. Let me have a closer look and will get back. Thanks.

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!

• Hasan: No, Verace. I would love to use a column value from another table as default for a table. NOT a constant. I know how to use constants as defaults and all the tricks. I think, I am not looking for a solution to the exact example I used. I am after a general solution using column names, not a constant.
• Vérace: Have you considered the use of the DOMAIN concept? It might be the closest thing in database DDL that corresponds to what you want. I'll write it up if you think it's an idea you might like to pursue? It doesn't work for MySQL (remember what I said about it using non-standard forms?) but is available for PostgreSQL and Firebird!
• Hasan: No, I did not. Could you please elaborate a bit? or give me some pointers where to look? Thanks.
• Vérace: I've included a bit about DOMAINs - I think it's probably the closest you can come to doing what you want, but MySQL doesn't have them. I think if this doesn't correspond to your needs, then you're going to have to look at TRIGGERs and procedural code - best of luck!