List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:July 28 1999 8:36pm
Subject:Re: making 2 columns in one table unique
View as plain text  
At 15:12, 19990728, mwkohout wrote:
>hi-I'm faced with a problem where I want 2 columns in one of my
>tables to be unique.  I was wondering if there was any way I
>could do this.....and if so, how

Make a UNIQUE key over both columns.  Or a PRIMARY KEY, if it's
appropriate.

For example, if you have a People table and a Projects table, and
you want to link them together (who's working on what projects),
you want:

    CREATE TABLE ProjectPeople (
        project_id MEDIUMINT UNSIGNED NOT NULL,
        person_id SMALLINT UNSIGNED NOT NULL,
        role ENUM('Grunt', 'Leader','Consultant') NOT NULL,
        PRIMARY KEY (project_id, person_id)
    )

Now there is a many-to-many relationship between People and Projects.
But there's no way for the same person to be listed twice on the
same project.

Tim
Thread
making 2 columns in one table uniquemwkohout29 Jul
  • Re: making 2 columns in one table uniquePaul DuBois29 Jul
    • Re: making 2 columns in one table uniquemwkohout29 Jul
  • Re: making 2 columns in one table uniqueThimble Smith29 Jul