sql script creating table with multiple primary keys


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: sql script creating table with multiple primary keys

  1. #1
    Join Date
    Jul 2006
    Posts
    91

    sql script creating table with multiple primary keys

    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,

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    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.

  3. #3
    Join Date
    Aug 2006
    Posts
    113
    indexed with no duplicates which is just like a primary key means what.

  4. #4
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    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.
    Last edited by Ron Weller; 02-05-2007 at 04:47 AM.

  5. #5
    Join Date
    Aug 2006
    Posts
    113
    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.

  6. #6
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    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.

  7. #7
    Join Date
    Aug 2006
    Posts
    113
    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

Similar Threads

  1. find version & service packs
    By rperez in forum Database
    Replies: 5
    Last Post: 01-02-2009, 05:14 PM
  2. Replies: 2
    Last Post: 08-23-2005, 03:19 PM
  3. Replies: 3
    Last Post: 02-12-2003, 10:31 AM
  4. Replies: 3
    Last Post: 08-20-2002, 01:14 AM
  5. Adding Primary Keys & Losing Triggers??
    By Beginner-Brad in forum Database
    Replies: 1
    Last Post: 01-27-2001, 05:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center