Database: INSERTS, speed and primary keys

[Monte Goulding]

Try:

-- -----------------------------------------------------
-- Table `handler`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `handler` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  `type` VARCHAR(255) NOT NULL ,
  `location` VARCHAR(255) NOT NULL ,
  `handler_num` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `mykey` (`name`, `type`, `location`, `handler_num`) );

Or you can use a separate Create Unique Index statement.

PS MYSQL Workbench is free, cross platform and make nice diagrams ;-) 

Cheers

Monte


On 27/09/2010, at 10:18 PM, David Bovill wrote:

> OK - not getting very far with that - Google is not my friend :)
> 
> Can you give me an idea of what the CREATE statement would look like?
> 
> CREATE TABLE 'handler' (
>>    'name' VARCHAR(255) NOT NULL,
> 
>    'type' VARCHAR(255) NOT NULL,
>>    'location' VARCHAR(255) NOT NULL,
>>    'handler_num' INTEGER NOT NULL,
>>    PRIMARY KEY ('name', 'type', 'location', 'handler_num')
>> );
>> 
> 
> With a surrogate key:
> 
> CREATE TABLE 'handler' (
>    'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
>    'name' VARCHAR(255) NOT NULL,
>    'type' VARCHAR(255) NOT NULL,
>    'location' VARCHAR(255) NOT NULL,
>    'handler_num' INTEGER NOT NULL,
> );
> 
> So how do I create a secondary key from "name,type,location,handler_num" and
> make sure that these combined columns are unique?
> 
> On 27 September 2010 12:50, Monte Goulding <monte at sweattechnologies.com>wrote:
> 
>>> HI Monte - hows down-under :)
>> 
>> Getting better all the time ;-)
>>> 
>>> Only by making the primary key = "name,type,location,hnum" instead of
>> "id"
>>> woudl you get a fast normalisation of the data inserted.
>> 
>> You can create an secondary key for those fields and still use your primary
>> key auto-increment field for a foreign key in other tables.
>> 
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution

--
Monte Goulding
M E R Goulding Software Development
Bespoke application development for vertical markets

InstallGadget - How to create an installer in 10 seconds
revObjective  - Making behavior scripts behave