SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Unique Records

  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2008
    Posts
    455
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Unique Records

    Hi guys,

    Is there a way of creating a unique 'group' of fields within a MySQL table?

    For example, take an address book.
    There will be fields like user id (the owner of the address) and post code. Is there a way of grouping the user id and the post code together to ensure that that user doesn't add that address again?
    Also, the user must be allowed to enter multiple addresses so the user id will appear multiple times.

    Any help would be appreciated.

    Cheers.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Create a unique index composed of userid and postcode.

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2008
    Posts
    455
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ALTER TABLE address_book ADD CONSTRAINT user_postcode UNIQUE (addr_postcode,user_id); ?

    This causes errors when INSERT is used with a user id that is already defined.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Looking at the syntax in the manual gives me headaches
    The CREATE INDEX syntax is easier.

    But maybe the index name has to be moved after UNIQUE, and maybe it makes a difference adding INDEX as well?
    Code:
    ALTER TABLE address_book ADD CONSTRAINT UNIQUE INDEX user_postcode  (addr_postcode,user_id);

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2008
    Posts
    455
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Quote Originally Posted by guido2004 View Post
    Looking at the syntax in the manual gives me headaches
    The CREATE INDEX syntax is easier.

    But maybe the index name has to be moved after UNIQUE, and maybe it makes a difference adding INDEX as well?
    Code:
    ALTER TABLE address_book ADD CONSTRAINT UNIQUE INDEX user_postcode  (addr_postcode,user_id);
    Thanks!! That does exactly what I want it to.
    You're right about the MySQL manual, it gives me headaches too.

    Thanks again!


Tags for this Thread

Bookmarks

Posting Permissions

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