Database: INSERTS, speed and primary keys
Monte Goulding
monte at sweattechnologies.com
Mon Sep 27 08:44:21 EDT 2010
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
More information about the use-livecode
mailing list