List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:January 2 2004 9:12am
Subject:Re: Primary Key
View as plain text  

> I saw an example of creating tables (see below).  I
> wonder what the primary key (user_name, role_name) in
> the table user_roles means?  Does it mean that both
> user_name and role_name are the primary key of the
> user_roles table?  How does a table have two primary
> keys?
> create table users (
>   user_name         varchar(15) not null primary key,
>   user_pass         varchar(15) not null
> );
> create table user_roles (
>   user_name         varchar(15) not null,
>   role_name         varchar(15) not null,
>   primary key (user_name, role_name)
> );

A table cannot have two primary keys, only zero or one.

>   primary key (user_name, role_name)

This primary key is a "compound" primary key - a constraint
for multiple columns. This means that every combination of
values needs to be unique.

These are valid (user, role):
martijn, admin
caroline, admin
caroline, poweruser

As you can see, you, as a user, can have multiple roles.
However, you cannot enter such a row twice:

caroline, poweruser
caroline, poweruser

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Upscene Productions

Primary KeyCaroline Jen2 Jan
  • Re: Primary KeyMartijn Tonies2 Jan