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

2021-12-26 00:00:00 sql database mysql database-design

我有两张桌子:

大学:

university_id(p.k) | university_name

和用户:

uid | name | university_id(f.k)

如何在用户表中保持university_id NULL?

How to keep university_id NULL in user table?

我只写了 1 个查询，我的查询是:

I am writting only 1 query, my query is:

INSERT INTO user (name, university_id) VALUES ($name, $university_id);

这里 $university_id 可以从前端为 null.

Here $university_id can be null from front end.

university 表将由我默认设置.

在前端，学生将选择大学名称，根据university_id将传递给user表，但如果学生没有选择任何大学名称，则应该将空值传递给 university_id 字段的 user 表.

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.

推荐答案

只允许表user的university_id列允许NULL值，所以你可以保存空值.

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)
)

更新 1

根据您的评论，您应该插入 NULL 而不是 ''.

based on your comment, you should be inserting NULL and not ''.

insert into user (name,university_id) values ('harjeet', NULL)

更新 2

$university_id = !empty($university_id) ? "'$university_id'" : "NULL";
insert into user (name,university_id) values ('harjeet', $university_id);

作为旁注，该查询容易受到 SQL 注入(s) 的变量来自外部.请查看下面的文章，了解如何预防.通过使用 PreparedStatements，您可以摆脱在值周围使用单引号.

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.

  • 如何在 PHP 中防止 SQL 注入?

相关文章