Database: INSERTS, speed and primary keys

[David Bovill]

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.
>