sql script creating table with multiple primary keys

[srinivasc_it]

Hi,

I am trying to execute following sql script in sql-server 2000 query analyzer

CREATE TABLE user_courses (user_id varchar(30) NOT NULL PRIMARY KEY,
course_id varchar(10) NOT NULL PRIMARY KEY)

Its give's me following error :-
Cannot specify multiple primary key constraint

Hence I am not able to ceate table with multiple primary keys. So can any one tell me how to get this done?.

Secondly, Primary key must be unique i.e duplicate values are not allowed in P.K field. But in this case since I am declaring two fileds as primary keys.
Will it allow me to have following records in the user_courses table?
user_id(P.K) course_id(P.K)
bob CRS235
alice CRS235
Tim CRS235
tom CRS635

So, if we consider both the fields as primary keys together than I am not voilating Uniqueness constraint. But, if I look at course_id alone then I am voilating uniqeness property?

Thanks,

[Ron Weller]

You can only have one primary key, either make all of the items a compound primary key, or make one a primary key and make the rest indexed with no duplicates which is just like a primary key.

[syedwna]

indexed with no duplicates which is just like a primary key means what.

[Ron Weller]

index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.

No Duplicates: To enforce uniqueness on the rows in a table. No two rows in a table can have the same value in a field that is Indexed with the no duplicates restraint.

A Primary Key of a table is not only automatically indexed; but is also restrained by the no duplicates restraint. This is to insure that the primary key always produces a unique record from within the table.

primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.

[syedwna]

i have understood many things from this reply.Thanks for that.

you said "A primary key is used to relate a table to foreign keys in other tables."

but what i know is
1)the column(table1) which is referring another column(table2) is called foreign key and the column which is referenced is called primary key.

[Ron Weller]

Exactly! that is what a foreign key is. It is when you store the primary key from one table in another table. This allows a link between the two tables. For example if you store the primary key from the customers table in the orders table then you can link the orders table to the customers table and for each order you can find the customer who placed that order.

[syedwna]

how many types of restraints are there like the one you said

1)no duplicates restraint


and

can u create a table for showing the syntax of
1)primary key
2)index
3)no duplicates restraint
etc