Setting a default value for a foreign key?
I have three tables in my database:
user (user_id(pk), username, email, password, country_code(fk), city_id(fk));
country (country_code(pk), country name);
city (city_id(pk), city_name);
The country and city tables are already filled with details. the user table is empty. All tables have a default value of "none" for each record.
And I have a simple interface that asks for an email and password to register.
So, when the user enters the email and password, mysql won't let you paste. It shows the following error:
Cannot add or update a child row: a foreign key constraint fails
This is because the table user
cannot insert values for country_code
and city_id
that are not in the table country
and city
. And the user hasn't given a value yet, the default is none, which is not in both parent tables.
So how to deal with integrity? Should I give NULL
as the default for foreign keys in the child table (i.e. user
)? I've tried giving the default values NULL and it works. I want to know what if it doesn't cause integrity issues in my database project? Is it correct?
source to share
With this construct, you can simply add a '(none)' entry for tables country
and city
.
Thus, you will have two "magic values", country_code
and city_id
associated with those "(none)" entries, which will be used as the default country_code (fk), city_id (fk) values in the new user table entries.
You can also avoid the "magic" (I mean constant values of special value) for country_code
and city_id
only hard wiring (none) values for both country_name
and city_name
and selecting the appropriate primary keys on average, where the criteria country_name
and city_name
.
source to share