Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2007
    Posts
    3

    Unanswered: script for 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
    May 2004
    Location
    Seattle
    Posts
    1,313
    you can't have two pks on a table, as the error indicates.

    you can, however, have as many unique constraints as you like.

    I think what you are really trying to do for the table above though, is to make a composite pk, which is described here:

    http://msdn2.microsoft.com/en-us/library/ms191236.aspx

    you would do it like this:

    Code:
    ALTER TABLE [mytable] ADD  CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED 
    (
    	[col1] asc,
            [col2] asc
    
    )
    see http://msdn2.microsoft.com/en-us/library/ms190273.aspx for details.

    a final note: if you have a student table, then really what you should do is create a pk on studentid on the student table, and a pk on courseid in the course table, and a fk on course.studentid to student.studentid. also it's weird to use strings like "bob" for a studentid. what do you do if you have two students named bob? usually you would use ints ids like this, and not strings. same for courses. int for the id, and strings for the names.

    EDIT: also, your composite pk idea falls over if the same student registers for same course twice (which happens - when I was a TA in physics classes I used to see the same pathetic pre-meds in my classes year after year - they kept failing or dropping out and retaking the class! One of my buddies used to call the Atwood machine the "Pre-Med Filtering Machine".)
    Last edited by jezemine; 02-01-07 at 15:05.

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    CREATE TABLE user_courses
    (
    user_id varchar(30) NOT NULL,
    course_id varchar(10) NOT NULL,
    PRIMARY KEY (user_id, course_id)
    )


    ddave

  4. #4
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by jezemine
    I used to see the same pathetic pre-meds in my classes year after year
    Doesn't this imply that you were also retaking that class year after year?

    Back to the subject at hand:

    I don't totally agree with jezemine's final note. The relationship between student and course is many to many, so having a studentid in the course table is no option. srinivasc_it's solution isn't a bad one, with the addition that both columns should also be declared separately as foreign keys.

    Fully agreeing on the ID-thing though.

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by Lexiflex
    Doesn't this imply that you were also retaking that class year after year?

    Back to the subject at hand:

    I don't totally agree with jezemine's final note. The relationship between student and course is many to many, so having a studentid in the course table is no option. srinivasc_it's solution isn't a bad one, with the addition that both columns should also be declared separately as foreign keys.

    Fully agreeing on the ID-thing though.
    I was teaching the class, not taking it. so it's allowed for me to be there year after year. i suppose you could criticize me as a bad teacher though, since some of my students didn't pass. or maybe they just liked me and so kept signing up for my physics 101 section... yea right.

    and you are right of course about the many-many thing. my goof.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •