1

I am working from this database, its one of the first I have tried building:

http://sqlfiddle.com/#!2/38ef8

When I try to add this line:

Insert Into country (name) values ('US');

It says Field 'id' doesn't have a default value. Am I doing my primary key correctly? I have seen people using "auto_incrment" on their primary key like this example:

http://sqlfiddle.com/#!2/c807a/2

Is that what I should be using?

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
Jimmy
  • 12,087
  • 28
  • 102
  • 192
  • Yes, either define the id as `AUTO_INCREMENT` or supply the id values during insert: `Insert Into country (id, name) values (1, 'US'), (2, 'FR');` – ypercubeᵀᴹ Dec 03 '12 at 09:42
  • "Is that what I should be using?" What you should use depends on your project requirements. – feeela Dec 03 '12 at 09:43
  • A primary key is a column or column set that uniquely identifies a row. The concept doesn't imply that the column value must be handled by the database server. – Álvaro González Dec 03 '12 at 09:47

4 Answers4

4

If you didn't specify PRIMARY KEY column as AUTO_INCREMENT then you have to give values manually, for example:

INSERT INTO Country(id, name) values(1, 'US');

It's up to you wheter use AUTO_INCREMENT or not. There are many reasons to do it and many not to do it:

Community
  • 1
  • 1
psur
  • 4,400
  • 26
  • 36
3

there are the properties of PRIMARY key

  1. cant be NULL
  2. cant be duplicate

now when you select AUTO_INCREMENT , every time you use the query

Insert Into country (name) values ('US');

it automatically generates a number incrementing the highest value existing in the table for the primary key column

but when you do not set the primary key as AUTO_INCREMENT ,

Insert Into country (name) values ('US');

this query will enter NULL values in every column for the row except the given column in that case your PRIMARY_KEY also gets a null value which clearly contradicts with the definition of PRIMARY_KEY . that is why you get the error

I hope the explanation serves

Hussain Akhtar Wahid 'Ghouri'
  • 6,527
  • 8
  • 50
  • 85
1

If you have not set your primary key as auto increment, you will have to insert that manually in your queries.

danish
  • 5,550
  • 2
  • 25
  • 28
0

The primary key should be set to AUTO_INCREMENT, if it is not so, you will have to set that manually. Although you can still insert with specific values after setting the primary key to AUTO_INCREMENT provided the key is not already existing :D

Rahul
  • 15,979
  • 4
  • 42
  • 63