primary, secondary keys

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

primary, secondary keys

markww
Hi all,
 Does sqlite allow multiple keys? When I created a table I did:
  CREATE TABLE test (name, address, fav_color, primary key(name, address))
 Is that valid? I would like to not have any duplicate name + address
entries. There
was no error reported but later I tried modifying the table in SQLite
Database Browser
and it complained that the table has multiple primary fields.
 Thanks,
Mark
Reply | Threaded
Open this post in threaded view
|

Re: primary, secondary keys

Dennis Cote
Mark Wyszomierski wrote:

>Hi all,
> Does sqlite allow multiple keys? When I created a table I did:
>  CREATE TABLE test (name, address, fav_color, primary key(name, address))
> Is that valid? I would like to not have any duplicate name + address
>entries. There
>was no error reported but later I tried modifying the table in SQLite
>Database Browser
>and it complained that the table has multiple primary fields.
> Thanks,
>Mark
>
>  
>
Mark,

Yes, that is valid SQL.

The error message means that you are trying to insert or update a row so
that its primary key (the combination of name and address) is the same
as some other row that already exists in the table. SQL requires that
the primary key of each row be unique.

HTH
Dennis Cote
Reply | Threaded
Open this post in threaded view
|

Re: primary, secondary keys

markww
Thanks Dennis

On 9/30/05, Dennis Cote <[hidden email]> wrote:

>
> Mark Wyszomierski wrote:
>
> >Hi all,
> > Does sqlite allow multiple keys? When I created a table I did:
> > CREATE TABLE test (name, address, fav_color, primary key(name, address))
> > Is that valid? I would like to not have any duplicate name + address
> >entries. There
> >was no error reported but later I tried modifying the table in SQLite
> >Database Browser
> >and it complained that the table has multiple primary fields.
> > Thanks,
> >Mark
> >
> >
> >
> Mark,
>
> Yes, that is valid SQL.
>
> The error message means that you are trying to insert or update a row so
> that its primary key (the combination of name and address) is the same
> as some other row that already exists in the table. SQL requires that
> the primary key of each row be unique.
>
> HTH
> Dennis Cote
>
Reply | Threaded
Open this post in threaded view
|

Re: primary, secondary keys

David M. Cook
In reply to this post by markww
On Fri, Sep 30, 2005 at 10:55:07AM -0400, Mark Wyszomierski wrote:

>  Does sqlite allow multiple keys? When I created a table I did:
>   CREATE TABLE test (name, address, fav_color, primary key(name, address))

I usually use UNIQUE, though, for "business keys", and reserve PRIMARY KEY
for the auto-increment field.

Dave Cook