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
