how to pass a null value to a foreign key field?
Solution 1
Just allow column university_id
of table user
to allow NULL
value so you can save nulls.
CREATE TABLE user
(
uid INT NOT NULL,
Name VARCHAR(30) NOT NULL,
university_ID INT NULL, -- <<== this will allow field to accept NULL
CONSTRAINT user_fk FOREIGN KEY (university_ID)
REFERENCES university(university_ID)
)
UPDATE 1
based on your comment, you should be inserting NULL
and not ''
.
insert into user (name,university_id) values ('harjeet', NULL)
UPDATE 2
$university_id = !empty($university_id) ? "'$university_id'" : "NULL";
insert into user (name,university_id) values ('harjeet', $university_id);
As a sidenote, the query is vulnerable with SQL Injection
if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements
you can get rid of using single quotes around values.
Solution 2
Here suppose i have foreign key user_id
and i want to insert null value for that.
Checkbox must be checked for insert null value for foreign key.
Solution 3
I was using MySQL InnoDB and even allowing NULL in the FK column and using NULL as default I was getting an error. So I used the SET syntax:
INSERT INTO (table) SET value1=X...
And I just don't set the FK column.
Comments
-
Harjeet Jadeja over 3 years
I have 2 tables:
university
:university_id(p.k) | university_name
and
user
:uid | name | university_id(f.k)
How to keep
university_id
NULL in user table?I am writting only 1 query, my query is:
INSERT INTO user (name, university_id) VALUES ($name, $university_id);
Here
$university_id
can be null from front end.university
table will be set bydefault by me.In the front end, student will select the university name, according to that the
university_id
will pass touser
table, but if student is not selecting any university name then is should pass null value to theuser
table foruniversity_id
field.