List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 9 2006 4:26pm
Subject:Re: How to not repeat fields
View as plain text  
At 17:17 +0100 10/9/06, Deckard wrote:
>Hi Paul,
>
>Now, i have this:
>
>CREATE TABLE wl_users(wl_user_id INT NOT NULL AUTO_INCREMENT PRIMARY
>KEY, name VARCHAR(255) NOT NULL, email VARCHAR(50) NOT NULL, password
>VARCHAR(40) NOT NULL, UNIQUE (wl_user_id, email)) TYPE=MyISAM;
>
>and the issue persists :(


You didn't try what I suggested.
The UNIQUE index should be UNIQUE (name, email, password).

>
>Best Regards,
>Deckard
>
>
>Paul DuBois wrote:
>>  At 22:43 +0100 10/8/06, Deckard wrote:
>>>  Hi,
>>>
>>>  Yes, it still allows duplicate (equal) rows to be inserted :(
>>>
>>>  Best Regards,
>>>  Deckard
>>
>>  I don't see the test case I asked for.
>>
>>  If you're seeing that records are duplicate except that they
>>  differ in ID value, that doesn't count as duplicate.  But in
>>  that case, I suggest that what you really want is a separate
>>  PRIMARY KEY on the wl_user_id column, and a UNIQUE index
>>  on (name, email, password).  That will prevent duplicate
>>  combinations of those three column values.
>>
>>
>>>
>>>  Paul DuBois wrote:
>>>>   At 22:21 +0100 10/8/06, Deckard wrote:
>>>>>   Hi,
>>>>>
>>>>>   Ok, naow i have this:
>>>>>
>>>>>   CREATE TABLE wl_users(wl_user_id INT NOT NULL AUTO_INCREMENT, name
>>>>>   VARCHAR(255) NOT NULL, email VARCHAR(50) NOT NULL, password
>>>>>  VARCHAR(32)
>>>>>   NOT NULL, PRIMARY KEY (wl_user_id, name, email, password))
>>>>>  TYPE=MyISAM;
>>>>>
>>>>>   and it lets repeated rows.
>>>>
>>>>   You have a PRIMARY KEY on all four columns in the table, so every
>>>>   combination of values for those four columns is required to be unique.
>>>>
>>>>   Therefore, if you have repeated rows in the table, that's very
> strange.
>>>>
>>>>   I claim you don't really have repeated rows. :-)
>>>>
>>>>   Can you provide a test case that shows repeats?
>>>>
>>>>>
>>>>>   How can i avoid the repeates rows ?
>>>>>
>>>>>   Best Regards,
>>>>>   Deckard
>>>>>
>>>>>   Paul DuBois wrote:
>>>>>>    At 22:11 +0100 10/8/06, Deckard wrote:
>>>>>>>    Hi,
>>>>>>>
>>>>>>>    How do i dreate a table, let's say:
>>>>>>>
>>>>>>>    CREATE TABLE wl_articles(wl_articles_id INT NOT NULL
> AUTO_INCREMENT
>>>>>>>    PRIMARY KEY, title TEXT NOT NULL,
>>>>>>>             main TEXT NOT NULL, extended TEXT NOT NULL,
> permalink
>>>>>>>    VARCHAR(255) NOT NULL, date DATE NOT NULL, ip
> VARCHAR(15),
>>>>>>>  user_agent
>>>>>>>    VARCHAR(50), status INTEGER NOT NULL,
>>>>>>>                     password VARCHAR(32) NOT NULL)
> TYPE=MyISAM;
>>>>>>>
>>>>>>>    without allowing INSERTs to repeat rows based on certain
> fields,
>>>>>>>  for
>>>>>>>    example, "main" and "status" ?
>>>>>>
>>>>>>    Normally, you would do this by creating a UNIQUE index on the
>>>>>>   combination
>>>>>>    of columns.  (Or you could use a PRIMARY KEY if both columns
> are NOT
>>>>>>   NULL.)
>>>>>>
>>>>>>    However, you cannot do what you want in this case.  Why? 
> Because
>>>>>>   main is
>>>>>>    a TEXT column, and TEXT columns allow a maximum of 255
> initial
>>>>>>   characters
>>>>>>    to be indexed.  That means you cannot use a UNIQUE index to
>>>>>>  provide a
>>>>>    > uniqueness constraint on anything from the 256th character
> on.
>>>>
>>
>>


-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Thread
How to not repeat fieldsDeckard8 Oct
  • Re: How to not repeat fieldsPaul DuBois8 Oct
    • Re: How to not repeat fieldsDeckard8 Oct
      • Re: How to not repeat fieldsPaul DuBois8 Oct
        • Re: How to not repeat fieldsDeckard8 Oct
          • Re: How to not repeat fieldsPaul DuBois9 Oct
            • Re: How to not repeat fieldsDeckard9 Oct
              • Re: How to not repeat fieldsPaul DuBois9 Oct
  • Re: How to not repeat fieldsMartijn Tonies9 Oct
  • Re: How to not repeat fieldsMartijn Tonies9 Oct
    • Re: How to not repeat fieldsDeckard9 Oct