Prevent null values are getting inserted into the table for primary key column

Question

I don't know whether it is problem with the database or what. For all the tables in my database, for the columns which are declared as primary key are taking NULL values or default values when query like

INSERT INTO TABLE (emp_name,emp_addr,emp_contact,salary) 
values ('Jack','127 N F 14 Stillwater','3456786543',8)

where the table consists of emp_id(primary key), emp_name, emp_addr, emp_contact and salary.

And if i am trying to insert a tuple with

INSERT INTO EMPLOYEE values (NULL,'Jack','127 N F 14 Stillwater','345678543',8)

, it is throwing the error that primary key cannot be null which is supposed to be.

My script for the table creation is..

    create table Employee(
    emp_id int(11) primary key,
    emp_name varchar(25) not null,
    emp_addr varchar(40) not null,
    emp_contact varchar(10),
    salary float(2)
    )ENGINE=INNODB;

This is what is happening in my DB
 create table sale(sale_id varchar(10) primary key not null,sold_vehicle varchar(12),sm_id int(11),                                                                sale_date date)ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into sale values('s2389','AP28DJ5093',1290,'05-12-2010');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into sale values('s2420','AP28MY0545',1290,'24-10-2012');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from sale;
+---------+--------------+-------+------------+
| sale_id | sold_vehicle | sm_id | sale_date  |
+---------+--------------+-------+------------+
| s2389   | AP28DJ5093   |  1290 | 0000-00-00 |
| s2420   | AP28MY0545   |  1290 | 0000-00-00 |
+---------+--------------+-------+------------+
2 rows in set (0.00 sec)

mysql> update sale set sale_id = null where sale_id = 's2389';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from sale;
+---------+--------------+-------+------------+
| sale_id | sold_vehicle | sm_id | sale_date  |
+---------+--------------+-------+------------+
|         | AP28DJ5093   |  1290 | 0000-00-00 |
| s2420   | AP28MY0545   |  1290 | 0000-00-00 |
+---------+--------------+-------+------------+
2 rows in set (0.00 sec)

mysql> desc sale;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| sale_id      | varchar(10) | NO   | PRI | NULL    |       |
| sold_vehicle | varchar(12) | YES  |     | NULL    |       |
| sm_id        | int(11)     | YES  |     | NULL    |       |
| sale_date    | date        | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

You sure you dont't have Auto-Increment on primary keys? – Vaibhav Desai Nov 13 '12 at 05:36 — Vaibhav Desai, Nov 13 '12 at 05:36
Why isnt ur primary key not null – smk Nov 13 '12 at 05:37 — smk, Nov 13 '12 at 05:37

score 2 · Answer 1 · answered Nov 13 '12 at 05:40

2

You can't have a primary key field to be null. And you must put not null for primary key. Otherwise what's the point of having a Primary key at all? :) Please change your CREATE QUERY TO THIS:

CREATE table tblblah(ID NOT NULL PRIMARY KEY, ...and so on

INSERT INTO table tblblah(ID, ....and so on

Fill it according to your table schema.

answered Nov 13 '12 at 05:40

bonCodigo

14,268
1
48
91

If you are not specifying you own values for ID field (e.g. Employee IC number, Staff Number...etc) then it's a good idea to have AUTO_INCREMENT clause included within the above. – bonCodigo Nov 13 '12 at 05:43
but primary key itself is a not null atribute. and it is shown in desc table as NO under NULL... do we need to explicitly give that constraint? I think we dont need to.. correct me if i am wrong – sunil kancharlapalli Nov 13 '12 at 05:44
Yes in MYSQL it's not a must to put NOT NULL. Sure PK is a 'not null' attribute in nature. Even though you do not wrap your field with 'not null' constraint pk should not permit nulls. That's why you get an error. (e.g. in your second INSERT query with NULL for primary key) Since you do not require auto increment but inserting your own value, then it's much safe and keep the integrity of your table to have explicit 'not null' constraint-so you do not depend on SQL engine- to avoid issues u faced here. [Ref](http://stackoverflow.com/questions/3905703/why-to-use-not-null-primary-key-in-tsql) – bonCodigo Nov 13 '12 at 06:11

score 0 · Answer 2 · answered Nov 13 '12 at 05:39

0

add AUTO_INCREMENT option on your table creation

create table Employee
(
emp_id int(11) AUTO_INCREMENT primary key,
emp_name varchar(25) not null,
emp_addr varchar(40) not null,
emp_contact varchar(10),
salary float(2)
)ENGINE=INNODB;

answered Nov 13 '12 at 05:39

John Woo

258,903
69
498
492

but i dont need auto increment of that key.. cant i have that option? – sunil kancharlapalli Nov 13 '12 at 05:41
if that's the case then remove `AUTO_INCREMENT` and supply an ID on it. `INSERT INTO EMPLOYEE values (1,'Jack','127 N F 14 Stillwater','345678543',8)` – John Woo Nov 13 '12 at 05:42

Prevent null values are getting inserted into the table for primary key column

2 Answers2