Re: Primary Key

[Martijn Tonies]

Hi,

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

(invalid):
caroline, poweruser
caroline, poweruser

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com