CHANGING primary key

[IBazz]

Hi,

I want to change my primary key from being one col to being three. I can't seem to find a way of 'change primary key'. all I find is 'drop' and 'create'.

With use of the MySQL manual I have created a script to replace the current PK with a new one.
I don't want to break the table so, would someone please advise me if this is correct.

ALTER TABLE `business_type`
DROP PRIMARY KEY
ADD INDEX business_type_ix PRIMARY KEY ( business_type, business_sub_type, business_catgeory ) PRIMARY KEY

bazz

[wibble wobble]

A RDBMS must always have a primary key, so I dont know if that would work. You might have to create a compound key with all 4 fields, then drop the original PK out of that.

Edit: Typo - RDBMS as RMDB

[r937]

I don't want to break the table so, would someone please advise me if this is correct.

one word: test database

break stuff to your heart's content, then apply it to your real database

[r937]

A RMDB must always have a primary key,

whatever an RDMB might be, that particular rule does not apply to any database system like MySQL, SQL Server, eck settera

[wibble wobble]

whatever an RDMB might be, that particular rule does not apply to any database system like MySQL, SQL Server, eck settera

Thanks for ripping into my typo

I never knew that second part - interesting.

[r937]

yeah, sorry, i know you meant RDBMS (relational database management system), i was just having you on

it's certainly easy enough to test -- you do have a test database for this stuff just like bazz has, right?

CREATE TABLE nopk ( foo INTEGER ) ;
INSERT INTO nopk VALUES ( 9 ) , ( 37 ) , ( 937 ) , ( 9 );
SELECT * FROM nopk;

[wibble wobble]

I have a scribbes DB (and a scribbles project in my IDE) if that counts!

Forget the rest of this post. I'm blind.

Did a few queries on PK-less tables. The only bug I got was getting 2 rows when there was only 1 match:

CREATE TABLE nopk ( foo INTEGER ) ;
INSERT INTO nopk VALUES ( 9 ) , ( 37 ) , ( 937 ) , ( 9 );
 
CREATE TABLE nopk2 ( foo2 INTEGER ) ;
INSERT INTO nopk2 VALUES ( 91 ) , ( 337 ) , ( 9437 ) , ( 9 );
 
SELECT * FROM `nopk2` JOIN `nopk` ON foo = foo2

That returned

foo2 	foo
9 	9
9 	9

[IBazz]

one word: test database

That's two words where I come from.

couldn't resist

anyway, I still get an error.

ALTER TABLE `business_type`
, DROP PRIMARY KEY
, ADD INDEX business_type_ix
, PRIMARY KEY (business_type,business_sub_type,business_category);

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' PRIMARY KEY (business_type,business_sub_type,business_category)' at line 4

I tried to do it with phpMyAdmin and was only able to create a unique index. when I then tried to remove the PK, it told me that I couldn't because there 'could only be one auto_increment column and it had to be the PK' (or words to that effect. Stands to reason I suppose.

And maybe I only need a unique index anyway? I am just trying to prevent duplicate entries and this has altered my table to createa unique index .

 ALTER TABLE `tbl_business_type` ADD UNIQUE `business_type_ix` ( `business_type` , `business_sub_type` , `business_category` )

not much like the docs said it should be done.
bazz

[r937]

the error message almost always tells you exactly where the error occurred

you forgot the ADD keyword in front of PRIMARY KEY

before you decide between primary and unique keys, you need to settle on whether you need a surrogate key (auto_increment) or not

[r937]

as for two columns in the same row called "type" and "subtype", you may want to rethink this

you're embedding a relationship (between type and subtype) into a third table, and you're leaving open the possibility that row can reference a type and subtype that aren't actually related to each other

better to link just to the (sub)type, and have the (sub)type link to its (super)type

see Categories and Subcategories

[IBazz]

Thanks Rudy.

I won't need the auto_increment in the end. But for now, and until I get my head around the alternative, I do need it. By alternative I mean where the three fields are normalised throughout the table and have PK/FK relationships within the table. (I'm sure there's a name for dat).

bazz