Thread: how to set more than two attributes as primary keys in a table
Hi!... Normally, there is only one attribute for the primary key but in my table, my primary key is composed of two attributes. How do I state this in SQL? What if Region_num and Num_Players, combined, should form a primary key? CREATE TABLE REGION ( Region_Num smallint primary key, Num_Players smallint, Player_Num smallint ); Thanks! __________________________________ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/
On Sun, Dec 28, 2003 at 20:21:32 -0800, Peggy Go <shatz_go@yahoo.com> wrote: > Hi!... > > Normally, there is only one attribute for the primary > key but in my table, my primary key is composed of two > attributes. How do I state this in SQL? What if > Region_num and Num_Players, combined, should form a > primary key? > CREATE TABLE REGION ( Region_Num smallint, Num_Players smallint, Player_Num smallint, primary key (Region_Num, Num_Players) );
On Sun, Dec 28, 2003 at 08:21:32PM -0800, Peggy Go wrote: > > Normally, there is only one attribute for the primary > key but in my table, my primary key is composed of two > attributes. How do I state this in SQL? What if > Region_num and Num_Players, combined, should form a > primary key? > > CREATE TABLE REGION ( > Region_Num smallint primary key, > Num_Players smallint, > Player_Num smallint > ); See the documentation for primary keys and for CREATE TABLE: http://www.postgresql.org/docs/current/static/ddl-constraints.html#AEN1972 http://www.postgresql.org/docs/current/static/sql-createtable.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Bruno Wolff III (Sunday 28 December 2003 23:24)
> primary key (Region_Num, Num_Players)
You might also find unique () to be very helpful. I personally prefer to
always have an id column as the primary key in any table, and to further
constrain the table with unique's, where necessary.
create table foobar (
id bigint default nextval(foobar_seq),
foo varchar(32),
bar smallint,
foob text,
primary key (id),
unique (foo, bar)
);
Vertu sæll,
--
Sigþór Björn Jarðarson (Casey Allen Shobe)
http://rivyn.livejournal.com
On Mon, Dec 29, 2003 at 10:35:54 -0500, Casey Allen Shobe <cshobe@softhome.net> wrote: > Bruno Wolff III (Sunday 28 December 2003 23:24) > > primary key (Region_Num, Num_Players) > > You might also find unique () to be very helpful. I personally prefer to > always have an id column as the primary key in any table, and to further > constrain the table with unique's, where necessary. > > create table foobar ( > id bigint default nextval(foobar_seq), > foo varchar(32), > bar smallint, > foob text, > primary key (id), > unique (foo, bar) > ); Note that isn't quite the same unless you add NOT NULL constraints for foo and bar.
Bruno Wolff III (Monday 29 December 2003 12:14) > Note that isn't quite the same unless you add NOT NULL constraints for > foo and bar. Oops! You're right, and the ID column needs a 'not null' as well. I just typed that in a hurry and forgot. Sorry. Vertu sæll, -- Sigþór Björn Jarðarson (Casey Allen Shobe) http://rivyn.livejournal.com
On Mon, Dec 29, 2003 at 12:09:01 -0500, Casey Allen Shobe <cshobe@softhome.net> wrote: > Bruno Wolff III (Monday 29 December 2003 12:14) > > Note that isn't quite the same unless you add NOT NULL constraints for > > foo and bar. > > Oops! You're right, and the ID column needs a 'not null' as well. I just > typed that in a hurry and forgot. Sorry. A primary key constraint implies both unique and not null, so you don't need to use NOT NULL on the ID column.
Bruno Wolff III (Monday 29 December 2003 12:21) > A primary key constraint implies both unique and not null, so you don't > need to use NOT NULL on the ID column. Man I'm really acting rusty today...sorry again :\ Vertu sæll, -- Sigþór Björn Jarðarson (Casey Allen Shobe) http://rivyn.livejournal.com