Learning SQL - Primary Keys
There is much more information on Primary Keys in the Database Design Tutorial section of this site.
Why Do We Need a Primary Key?
We need Primary Keys to be able to uniquely identify a row of data. If we want to update an existing row of data in our table we need to be able to tell the database which row. For example if one of our parents, Say Linda Brown, in our example database (you have followed all the lessons so far haven't you?) moved away and we wanted to delete her record we could execute this code:
delete from QiSQL_Parents where last_name ='Brown';
That code would work fine - the only problem is we have TWO parents named Brown, so they would both be deleted! So let's re-insert the values we have just deleted:
insert into QiSQL_Parents ( first_name , last_name ) values ( 'Linda' , 'Brown' );
insert into QiSQL_Parents ( first_name , last_name ) values ( 'Susan' , 'Brown' );
We should have used the Primary Key to identify the record we wanted to delete. First we find the key using a select statement:
select * from QiSQL_Parents;
Linda Brown's ID filed (the primary Key) shows as 3. although it may be different in your database if you have played around with the data or not followed the lessons is order (that's OK, it's your data!). So now we can delete the exact record we were looking for.
delete from QiSQL_Parents where id = 3;
Another important reason to use Primary Keys is they link to Foreign Keys (the next lesson) - and Foreign Keys are the glue that holds Relational Databases together!
When you create an Integer Primary Key column in a SQLite Table you can add the keyword Autoincrement.
For a 'Normal Integer Primary Key column - SQLite will try and give you a value equal to the highest Rowid CURRENTLY in the table plus 1. Rowids may be re-used.
For an Integer Primary Key AutoIncrement column - SQLite will try and give you a value equal to the highest Rowid EVER USED in the table plus 1. Rowids should never be re-used. There is an extra overhead on Inserts.