Hey all. I'm a straight up SQL newb. I googled and searched Stack Overflow on how to create a primary key column on an existing table and got this:

ALTER TABLE myTable ADD id INT IDENTITY
CONSTRAINT id _pk PRIMARY KEY;

But I get syntax errors. Is there anything wrong with just doing this:

1, create new column
2. write a php loop to add incremental numbers to column
3. classify the new column as auto-incremental, non-null, and primary

I know how to do the first two...and #3 is probably pretty googleable

Thanks for the help!

Additional information: on MySQL 5.2. My table currently has 7 columns and 300ish records. Table will probably grow to about 500 records. The table represents my vinyl record collection, which I find amusing (records of records)

This could also be happening because you are trying to add a new column to existing data and trying to call that your primary key, and that would throw an error as far as I know. You would be able to ALTER and existing table to make a column your primary key, but if you have repeating data in the column you are attempting to alter, then you'll end up with an error.

I would just create a new table with the data as you want it.

something like:

create table myRecords
(id int auto_increment, record_name varchar, artist varchar, etc etc etc);

http://dev.mysql.com/doc/refman/5.0/...increment.html

then:

insert into myRecords
(record_name, artist, etc etc etc)
select
record_name, artist
from myOtherTable;

http://forums.mysql.com/read.php?20,248481

This is my quick and dirty. Normally use PostgreSQL, the syntax is a little bit different, and I didn't bother to do a full translation, so don't copy/paste and use what I have written above.

BTW, you use primary and multicolumn keys if you want to stop yourself from adding duplicates. In your database, you may consider creating multi-column keys on (record_name), (artist), and (song) for example. Creating a primary key on the increment id will not prevent duplicates in your database.

edit to add: to clarify, primary keys are used only for preventing duplicates. Same goes for multi-column keys.

'nother edit: To be honest, unless you have some good reason to have an id_column for your database, I wouldn't bother with it.

Excellent comments.

OP, are you wondering how primary keys work or are you trying to solve some problem in your database?

^ I am trying to solve a problem.

I have a dynamically generated table that represents my "inventory" or music collection. When someone clicks an album title, a modal window pops up with additional information, some from the Last.fm API, and some from my own database (such as condition, catalog number, and additional notes).

The content from the modal window is all generated from its own php file, so it isn't like i'm inside the same table generating loop I was on the inventory page, but I can pass the modal php any parameters I want. I figured if I passed it an ID or primary key, I could then easily look up the record in my modal php and retreive the condition, catalog, notes, etc information I need.

a link to the page in progress. The information I need is currently static/hard coded...as I am also working on the modal window's layout:
http://alamorecords.coolpage.biz/inventory.php

to clarify, without some sort of INT column with unique numbers, I'd have to pass the modal window like 7 parameters through links, like:

modal.php?artist='souljaboy'&condition='dees'&cat= '#42069getit'&notes='bla%20blah' etc etc

If i use a key, i'd imagine i'd just go:

modal.php?id='80'

Then in my modal php I could query myself to id=80, and pull all the relevant columns.

you want an "id_column" in almost any database table imo. primary keys are not used for preventing dupes (although a primary key can never contain a dupe), they are used for uniquely identifying a given row.

any recommendations on how to add an id column to an existing table?

I was looking at this page and at first I though the catalog # was generated by last.fm, and then I see the ones I looked at are all the same, so they are generated by you? And is this the entire point of your op?

I'm not sure how the last.fm works into all of this, but is there some SQL commands that come with the plug-in?

So, assuming the last.fm has it's own catalog identifier, you can create this relation:

create table myRecords
(id int serial, album char(), artist char(), year year, format char(), price numeric, fmSerial text default null)

then refer to my first post and transfer your data.

then if you have the database for last.fm:

update myRecords
set myRecords.fmSerial = theirRecords.serial
(select abum, artist, serial
from fmRecords) as theirRecords
where myRecords.album = theirRecords.album
and myRecords.artist = theirRecords.artist;

Now, you can do:

select fmSerial
from myRecords
where id = ****;

This may be over-complication though, but I'm still a tad bit blind and hoping this will help you think about your issue a little bit.

I knew there would be some disagreement about my over-simplified definition about primary_key. Rather focus on the functionality of this over the design theory of it.

How would you keep track of which is which. You'd still have to:

select id
from myRecords
where artist = 'souljaboy'
and album = '????';

in order to get the id in the first place. I'm not sure if you are creating an extra query step that won't work out like you think it will.

Are you thinking of creating a few tables? One for the main page, and one with the extra information?

You could create a new column, then loop out numbers into the column, and insert them. You cannot make this new column your primary_key.

What's with all the ****ty MySQL advice lately on really trivial problems? A simple google search shows it's pretty easy: alter table myRecords add column id int auto_increment first, add primary key(id);

Quick irrelevant question, I don't really know anything about queries to alter/create tables in a database because I've never ever needed to do this (well, on very rare occasions). I see quite a lot of questions about it, can someone tell me what sort of real life situations you need to write these sorts of queries?

Sorry I took a week off...but this worked perfect. I really did google around I swear but kept getting syntax errors.

Thanks again!

Honestly...I just moved to a new web server that uses PHPmyAdmin and I find it kinda confusing. It is easier for me to understand the actual queries than the phpmyadmin interface.

edit: for some things...

Its pretty common for schemas to change. Probably not primary keys, but certainly adding columns and indexes.

In most cases its because you have decided to store some additional information which does not already have a column. We do this all the time where I work.

One of the beauties of NoSQL databases is that all this can go out the window

I use MS-SQL and use Enterprise Manager to alter tables in a visual way, this is 10x quicker and easier than writing queries. Do other databases not have similar tools?