如何将空值传递给外键字段?

时间:2021-08-26 11:49:58

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,可以避免在值周围使用单引号。

#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,可以避免在值周围使用单引号。

#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列。