5

I have the following table: 

mysql> DESC my_contacts;  
+----------+-------------+------+-----+---------+-------+  
| Field    | Type        | Null | Key | Default | Extra |  
+----------+-------------+------+-----+---------+-------+  
| id       | varchar(20) | NO   | PRI |         |       |  
| location | varchar(20) | YES  |     | NULL    |       |  
| city     | varchar(20) | YES  |     | NULL    |       |  
| state    | varchar(2)  | YES  |     | NULL    |       |  
+----------+-------------+------+-----+---------+-------+  
4 rows in set (0.01 sec)

If I do a select all I get: 

mysql> SELECT * FROM my_contacts;  
+----+--------------+------+-------+  
| id | location     | city | state |  
+----+--------------+------+-------+  
| 1  | Chester,NJ   | NULL | NULL  |  
| 2  | Katy,TX      | NULL | NULL  |  
| 3  | San Mateo,CA | NULL | NULL  |  
+----+--------------+------+-------+  
3 rows in set (0.00 sec)

I run the following command: 

INSERT INTO my_contacts (city,state)  
VALUES  
(SUBSTRING_INDEX(location,',',1),RIGHT(location,2));

My purpose was to populate the columns city and state with the part before the comma and the part after the comma from the location column.
But the following happened to my table: 

mysql> INSERT INTO my_contacts (city,state)  
    -> VALUES  
    -> (SUBSTRING_INDEX(location,',',1),RIGHT(location,2));  
Query OK, 1 row affected (0.02 sec)   

mysql> SELECT * FROM my_contacts;  
+----+--------------+------+-------+  
| id | location     | city | state |  
+----+--------------+------+-------+  
|    | NULL         | NULL | NULL  |  
| 1  | Chester,NJ   | NULL | NULL  |  
| 2  | Katy,TX      | NULL | NULL  |  
| 3  | San Mateo,CA | NULL | NULL  |  
+----+--------------+------+-------+  
4 rows in set (0.00 sec)

I get a record and the id which is the primary key is empty. How is this possible?
I mean it is not NULL but a primary key is not supposed to be empty either right?

AstroCB
  • 12,337
  • 20
  • 57
  • 73
Cratylus
  • 52,998
  • 69
  • 209
  • 339

6 Answers6

10

You defined your id field as a varchar, which is a dumb idea when you're using it to store integers. an empty field is NOT null. a zero-length string is still a valid string, and therefore a valid id value as far as your table is concerned. Try inserting ANOTHER blank string and you'll get a primary key violation:

INSERT INTO yourtable (id) VALUES (''); // will not work

The id field should be an int type. That'd disallow "empty" values.

Marc B
  • 356,200
  • 43
  • 426
  • 500
2

primary keys are unique so if you alter the table, then the second row will attempt to add an empty value and fail. as a result, it will attempt the next possible value. If you want the first value not to be empty, you can set a default value.

Youn Elan
  • 2,379
  • 3
  • 23
  • 32
1

It's not empty. It's probably an empty string. Note that the datatype is varchar(20).

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
1

Well, you didn't assign a value to the primary key field, so the default is NULL. . You want to modify the table so the primary key is auto_increment.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You can use a varchar as a foreign key related to another database table, but if you wish to use it as a numerical key, you should utilize a numerical data type such as int.

Nathan Rackley
  • 11
  • 1
0

I know this doesn't answer the precise question regarding the primary key, but as your question does point out the fact you are also having issues parsing out the city and state from your location column, here's the query you would want to use (note you want an UPDATE to modify existing rows, not an INSERT which will add new rows rather than columns):

UPDATE my_contacts
SET
    city = substr(location, 1, locate(',', location) - 1),
    state = substr(location, locate(',', location) + 1);