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 DEFAULT
s, 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!
state
table always have 1 row?select p.person_name,s.state_name, p.profession from person p join state s;
is that what you want ?