CODEWITHSUNDEEP
mysqlsql
Shad
Shad

Reputation: 1219

Confusion Regarding Primary Key in Mysql?

I am learning about SQL and using Mysql to work out the queries. On topic of Primary Key, I read that

Whichever column is declared as Primary Key, it can not be null.

So, I made a simple employee table with fields id and name with PRIMARY KEY as id;

And put in the following queries, 

INSERT INTO employee (id, name) VALUES (1, 'John');
INSERT INTO employee (id, name) VALUES (2, 'Frank');

Now to test the primary key column not being Null property I put in the following query.

INSERT INTO employee(name) VALUES('Joe');

Turns out no error was shown and the query ran successfully

Upon selecting the records from the whole table I see

id |name
0  | Joe
1  | John
2  | Frank

I was expecting to see error something like

Column 'id' cannot be NULL

but as you can see, Primary Key for Joe was automatically set to 0. I try inserting again it with not giving id

Now I get the message,

Duplicate entry for '0' for key 'PRIMARY'

So what happened here? And Doesn't Primary key begins from 1?

Upvotes: 1

Views: 531

Answers (2)

Solarflare
Solarflare

Reputation: 11106

MySQL (before 5.7) uses an implicit default value of 0 for your primary key, see the documentation:

If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. Exception: If the column is defined as part of a PRIMARY KEY but not explicitly as NOT NULL, MySQL creates it as a NOT NULL column (because PRIMARY KEY columns must be NOT NULL). Before MySQL 5.7.3, the column is also assigned a DEFAULT clause using the implicit default value. To prevent this, include an explicit NOT NULL in the definition of any PRIMARY KEY column.

If you insert null into such a column, the implicit value is taken, unless you set strict mode:

For data entry into a NOT NULL column that has no explicit DEFAULT clause [...]

  • If strict SQL mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.

  • If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

The implicit default value for numeric types is 0. So every time you use null (or not use the column at all), MySQL uses 0. This works the first time, but not the second time.

Upvotes: 2

giorgio
giorgio

Reputation: 10202

You've set the AUTO_INCREMENT attribute on the id field. That's a nice little feature that ensures the value is always unique so you don't have to worry about it being null, and worry about stuff that matters. But if you try this, errors will surely be thrown at you;

INSERT INTO `employee` (`id`, `name`) VALUES (null, `Joe`);

EDIT Ah, I didn't really see the error you posted. What happened here; you did actually not set the AUTO_INCREMENT attribute (although you should have ;)). Now, the id field is an integer field. If you do not give any value, mysql tries to save your ass by giving it the most probable value, or most close to no value at all, which is 0. The first time all is well, the second time it sees the value is already present, and it fails.

Upvotes: 1

Related Questions