SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: Unique key pair

  1. #1
    Grumpy Minimalist
    Join Date
    Jul 2006
    Location
    Ontario, Canada
    Posts
    424
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unique key pair

    Hi,

    I'm very new to MySQL, and therefore don't even know what to search for when it comes to this issue (I've tried about a dozen terms to no avail).

    My table is set up like this:
    Code:
    CREATE TABLE `tableName` (
      `sessionID` int(10) unsigned NOT NULL default 0,
      `name` varchar(40) default NULL,
      `value` longtext NOT NULL)
    ENGINE=InnoDB DEFAULT CHARSET=latin1
    `sessionID` actually corresponds to another value in a different table. From what I understand, I've set up a "one-to-many relationship" (`sessionID` must be unique in the other table, but not this one). However, I would like to add some kind of a constraint that requires that `name` is unique within the context of it's `sessionID` value.

    For example, this would be allowed:
    Code:
    `sessionID`	`name`		`value`
    1		'thing1'	'stuff'
    1		'thing2'	'more stuff'
    2		'thingy'	'stuff'
    2		'thing2'	'blahblah'
    But this would not, because there would be two identical `name` values ('thing2') for one `sessionID` value (2):
    Code:
    `sessionID`	`name`		`value`
    1		'thing1'	'stuff'
    1		'thing2'	'more stuff'
    2		'thingy'	'stuff'
    2		'thing2'	'blahblah'
    2		'thing2'	'error'
    Is this possible, and if so, how can I state this in my CREATE TABLE query? Also, does this have a specific name (so I can look up more information if need be)?

    Thanks!

  2. #2
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What you're looking for is making `name` and `sessionID` a unique key. I've never done that in the CREATE TABLE syntax, although im sure its possible, but after you create the table you can just run:

    ALTER TABLE `tableName` ADD UNIQUE ( `sessionID` ,
    `name`
    )

    Then if you try to INSERT a row with a sessionID AND name that is already in the table you will get an error.


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
  •