Re: Creating a table with two keys?

Brian Master wrote:
>
> I know this is probably a simple question, but how do I create a table
> with two primary keys? Here is what I tried, but it doesn't work:
>
> create table class
> (
> semester_code char(3) not null primary key,
> course_num smallint not null primary key,
> hours float not null
> )
>
> Thanks for any help,
>
> Brian Master

Brian,

Unless I miss my guess, what you want to do is create the table with a 2-part (composite) key. In this case, the combination of 'semester_code' and 'course_num' should be unique, not (as your statement shows) the 'semester_code' by itself and the 'course_num' by itself.

If that is correct, the statement becomes:

create table class (

     semester_code   char(3),
     course_num      smallint,
     hours           float       not null,
     primary key (semester_code, course_num)

Note that being primary key implies 'not null', therefore that is not required.

If course, if you really want to have a unique semester_code and a unique course_num, then I'd suggest

create table class (

     semester_code   char(3),
     course_num      smallint    not null,
     hours           float       not null,
     constraint class_pk primary key (semester_code)

create unique index abc on class ( course_num);

Note also, placing constraints on the same line as the the column is optional - my personal preference is to place all constraints at the end of the column list. I don't think there is any performance penalty doing it this way, as the resulting constraints seem to be identical.

/Hans Received on Sun Oct 27 1996 - 00:00:00 CEST