33

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?

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

simhumileco
  • 31,877
  • 16
  • 137
  • 115
Harjeet Jadeja
  • 1,594
  • 4
  • 19
  • 39
  • you can edit database field and allow null value to be insert. – Devang Rathod Feb 26 '13 at 06:39
  • To say that a field is a [foreign key](http://www.w3schools.com/sql/sql_foreignkey.asp) means it is constrained to match a primary key in the specified table. So it cannot be Null in that case (as matching makes no sense with Null values). Perhaps you should back up and explain the purpose of your question. – hardmath Feb 26 '13 at 06:40
  • 1
    @hardmath *..So it cannot be Null in that case (as matching makes no sense with Null values)..* -- that is not always true. Foreign keys can be nullable. – John Woo Feb 26 '13 at 06:44
  • 1
    @JW: Indeed foreign key fields can be nullable (see [this previous question](http://stackoverflow.com/questions/441988/mysql-foreign-key-to-allow-null)) but the foreign key constraint is not enforced with Null values. – hardmath Feb 26 '13 at 06:49
  • @hardmath http://www.sqlfiddle.com/#!2/38bc8 – John Woo Feb 26 '13 at 06:53
  • 2
    @JW: Based on the edit the OP made to the question, and the comment made to your answer, the issue arises because the frontend supplies an empty string rather than a valid university ID for an INSERT. Possibly the frontend should be modified to provide the required foreign key, and possibly the database scheme should be changed to silently accept this constraint failure. – hardmath Feb 26 '13 at 07:06
  • @JW: The previous question I linked to above presents a situation in which there is a very clear rationale for the kind of one-to-zero-or-one "business rule" you speak of below. So far the OP hasn't given us that much of an explanation, e.g. when the university ID on the user record will be supplied, if not at the time INSERT is done. – hardmath Feb 26 '13 at 07:12
  • `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. – Harjeet Jadeja Feb 26 '13 at 07:22

3 Answers3

53

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

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

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

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.

Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • i have allowed null value but when i writting this query its showing error: `insert into user (name,university_id) values ('harjeet','')` – Harjeet Jadeja Feb 26 '13 at 06:42
  • See my comment on the Question. A Null value cannot satisfy the foreign key constraint. – hardmath Feb 26 '13 at 06:43
  • 1
    no, an empty string is not the same with null as empty string has value but just empty while null has nothing on it. you should specify it as `NULL` eg, `insert into user (name,university_id) values ('harjeet',NULL)` – John Woo Feb 26 '13 at 06:43
  • @hardmath *..A Null value cannot satisfy the foreign key constraint...** -- no. that's why we have `one-to-zero-or-one relation`. – John Woo Feb 26 '13 at 06:46
  • 1
    so finally I have to go with modification in UI only :( . – Harjeet Jadeja Feb 26 '13 at 07:43
4

Here suppose i have foreign key user_id and i want to insert null value for that.

enter image description here

Checkbox must be checked for insert null value for foreign key.

Robert Sinclair
  • 4,550
  • 2
  • 44
  • 46
Devang Rathod
  • 6,650
  • 2
  • 23
  • 32
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:

INSERT INTO (table) SET value1=X...

And I just don't set the FK column.

G_comp
  • 162
  • 3
  • 12