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?
如何在用户表中保存university_id NULL ?
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_id可以从前端为空。
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 to user
table, but if student is not selecting any university name then is should pass null value to the user
table for university_id
field.
在前端,学生将选择大学名称,根据这一点,university_id将传递给用户表,但是如果学生没有选择任何大学名称,则应该将null值传递给university_id字段的用户表。
3 个解决方案
#1
39
Just allow column university_id
of table user
to allow NULL
value so you can save nulls.
只允许表用户的列university_id允许空值,以便保存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
更新1
based on your comment, you should be inserting NULL
and not ''
.
根据您的评论,您应该插入NULL而不是。
insert into user (name,university_id) values ('harjeet', NULL)
UPDATE 2
更新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.
作为旁注,如果变量的值来自外部,那么查询就会受到SQL注入的影响。请看下面的文章,学习如何预防。通过使用preparedstatement,可以避免在值周围使用单引号。
- How to prevent SQL injection in PHP?
- 如何防止PHP中的SQL注入?
#2
4
Here suppose i have foreign key user_id
and i want to insert null value for that.
这里假设我有外键user_id我想为它插入空值。
Checkbox must be checked for insert null value for foreign key.
必须为外键检查插入空值复选框。
#3
1
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:
我使用的是MySQL InnoDB,甚至在FK列中允许NULL,使用NULL作为默认值,我得到了一个错误。所以我使用了集合语法
INSERT INTO (table) SET value1=X...
And I just don't set the FK column.
我只是不设置FK列。
#1
39
Just allow column university_id
of table user
to allow NULL
value so you can save nulls.
只允许表用户的列university_id允许空值,以便保存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
更新1
based on your comment, you should be inserting NULL
and not ''
.
根据您的评论,您应该插入NULL而不是。
insert into user (name,university_id) values ('harjeet', NULL)
UPDATE 2
更新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.
作为旁注,如果变量的值来自外部,那么查询就会受到SQL注入的影响。请看下面的文章,学习如何预防。通过使用preparedstatement,可以避免在值周围使用单引号。
- How to prevent SQL injection in PHP?
- 如何防止PHP中的SQL注入?
#2
4
Here suppose i have foreign key user_id
and i want to insert null value for that.
这里假设我有外键user_id我想为它插入空值。
Checkbox must be checked for insert null value for foreign key.
必须为外键检查插入空值复选框。
#3
1
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:
我使用的是MySQL InnoDB,甚至在FK列中允许NULL,使用NULL作为默认值,我得到了一个错误。所以我使用了集合语法
INSERT INTO (table) SET value1=X...
And I just don't set the FK column.
我只是不设置FK列。