如何传递一个空值到外键字段？ [英] how to pass a null value to a foreign key field?

问题描述

我有两张表：

大学：

university_id | university_name

用户：

名称| university_id（fk）

如何保持 university_id NULL

我只写一个查询，我的查询是：
insert into user（name，university_id） values（$ name，$ university_id）;

I am writting only 1 query, my query is: insert into user (name, university_id) values ($name, $university_id);

这里$ university_id可以从前端为空。

here $university_id can be null from front end.

大学表将由我设置默认。

在前端，学生将选择大学名称，根据 university_id 将传递到用户表，但如果学生未选择任何大学名称，则应将null值传递字段中的用户表。 student_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 user 允许 NULL 值，以便您可以保存 nulls 。

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

插入 NULL ，而不是''。

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

作为旁注，查询容易受到 s ）来自外部，则.com /rel =noreferrer> SQL注入 。请查看下面的文章，了解如何防止它。通过使用 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.

• a href =http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php>如何在PHP中防止SQL注入？

这篇关于如何传递一个空值到外键字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！