John,
Here is an example which should clarify for you:
INSERT INTO testTable (a,b) VALUES ('AVAL', 'BVAL');
Let's say testTable looks like this:
+-------------+
| a | b |
--------------|
| AVAL | EVAL |
| FVAL | BVAL |
+-------------+
You have your unique index over (a,b). The insert statement above will
succeed because there is no entry in testTable where _both_ a and b are as
specified in the INSERT.
The table should then look like:
+-------------+
| a | b |
--------------|
| AVAL | EVAL |
| FVAL | BVAL |
| AVAL | BVAL |
+-------------+
Any of the following INSERT statements would now fail:
INSERT INTO testTable (a,b) VALUES ('AVAL', 'BVAL');
INSERT INTO testTable (a,b) VALUES ('AVAL', 'EVAL');
INSERT INTO testTable (a,b) VALUES ('FVAL', 'BVAL');
Hope this helps,
Regards,
Lachlan
PS. Please remember to reply to the [EMAIL PROTECTED] address rather
than my direct address, this way everyone can keep track of the discussion.
-----Original Message-----
From: John Mistler [mailto:[EMAIL PROTECTED]
Sent: Thursday, 8 July 2004 1:24 PM
To: Lachlan Mulcahy
Subject: Re: INSERT DISTINCT?
Thanks, Lachlan! One other thing I am wondering: given two columns (a,b)
with a UNIQUE multi-column index over them, will an insert fail if a row
with column a's data already exists, and the data for column b does not? I
am wanting to only prevent inserts when BOTH a & b's data already exists.
Thanks,
John
on 7/7/04 7:46 PM, Lachlan Mulcahy at [EMAIL PROTECTED] wrote:
> John,
>
> As far as I'm aware there is no problem creating a multi-column unique
index
> on fields that have already been indexed seperately. The multi column
index
> will be used in situations where you are trying to find unique
> _combinations_ of the two fields and the individual indexes in situations
> where you are trying to find instances of one OR the other.
>
> If you are doing a number of inserts or multi-row inserts you may want to
> use the "IGNORE" parameter. This will allow the INSERT command to complete
> and return successfully, simply skipping the rows where dupes are found,
> otherwise mysql is going to generate a duplicate key error when duplicates
> are found.
>
> Hope this clarifies some things for you.
>
> Lachlan
>
> -----Original Message-----
> From: John Mistler [mailto:[EMAIL PROTECTED]
> Sent: Thursday, 8 July 2004 12:27 PM
> To: [EMAIL PROTECTED]
> Subject: Re: INSERT DISTINCT?
>
>
> I am not certain from the documentation whether it is advisable to create
a
> unique multi-column index on two columns that are already individually
> indexed. The individual indexes I assume I need for when I do a SELECT on
> those particular columns. The multi-column one I need for the reasons
> discussed below. Any one know?
>
> Thanks,
>
> John
>
> on 7/7/04 2:21 PM, Joshua J. Kugler at [EMAIL PROTECTED] wrote:
>
>> Certainly, it's called making a unique index on the field(s) you want to
> keep
>> unique.
>>
>> Hope that helps.
>>
>> j----- k-----
>>
>> On Wednesday 07 July 2004 12:48 pm, John Mistler said something like:
>>> Is there a way to do an INSERT on a table only if no row already exists
>>> with the same info for one or more of the columns as the row to be
>>> inserted? That is, without using a method outside SQL?
>>>
>>> Thanks,
>>>
>>> John
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
