如何传递一个空值到外键字段? [英] 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屋!