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

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.
>>>
> 
> 

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