#1
  1. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,146
    Rep Power
    144

    UNIQUE CONTRAINT on two columns?


    Hi--

    Long story short, I have a junction table (?) that tracks what classes students are registered for. Despite all my code to prevent duplicate entries, some people still manage to sign up for the same class twice.

    My table:

    Code:
    SCHEDULED_CLASSES
    
    
    pkey     class_key     student_key     scheduled_as
    -------------------------------------------------------------------
    1           1                   1                      s
    2           2                   1                      s
    3           3                   2                      c
    4           3                   2                      c
    I need to prevent row 4 from being inserted since student 2 is already scheduled for class 3.

    Is this possible using a UNIQUE CONSTRAINT or any other method?


    Oh, that last column just indicates if the student signed up as a Single or Couple and should not be a factor in duplicate entries.

    Thank you!
    Last edited by Frank Grimes; July 11th, 2017 at 02:12 PM.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,434
    Rep Power
    9645
    Sure.
    Code:
    ALTER TABLE SCHEDULED_CLASSES ADD UNIQUE KEY (student_key, scheduled_as)
    Primary keys, unique keys, and indexes all can be spread out over multiple columns. They function as indexes using columns from left to right, so
    Code:
    SELECT * FROM SCHEDULED_CLASSES WHERE student_key = 2
    SELECT * FROM SCHEDULED_CLASSES WHERE student_key = 2 AND scheduled_as = 'c'
    will both use the index while
    Code:
    SELECT * FROM SCHEDULED_CLASSES WHERE scheduled_as = 'c'
    will not, so when using multiple columns you need to consider the ordering. You can, of course, add a separate index to cover other cases - here that would be on just scheduled_as, but that doesn't sound really appropriate.

    Comments on this post

    • Frank Grimes agrees
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    you got the wrong columns in your unique constraint, mister requinix

    it should be class_key and student_key

    aside to Frank -- why the auto_increment?

    if you hade made class_key and student_key a composite primary key, none of this would've come up
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,146
    Rep Power
    144
    Originally Posted by r937
    you got the wrong columns in your unique constraint, mister requinix

    it should be class_key and student_key

    aside to Frank -- why the auto_increment?

    if you hade made class_key and student_key a composite primary key, none of this would've come up


    Ha. Thanks. You beat me to it. I still see what Requinix was doing.

    Regarding the auto_increment...I really don't know. It's very possible this table served a different function when it was created; this project is miles from where it started and completely unrecognizable from its beginning. If I had this build from scratch, I'd do all very differently.

    And now you're making me look up "composite primary key".
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence

IMN logo majestic logo threadwatch logo seochat tools logo