List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 9 2006 3:51pm
Subject:Re: How to not repeat fields
View as plain text  
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