In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order."
So, yes, the multi-column index on A,B will serve as the necessary index to make A a foreign key. You can still drop the single column index on A.
Michael
Emmett Bishop wrote:
Paul,
What about the case where column A is a foreign key? In that case would you be forced to keep the index on column A or could you use the A,B index since A is the left most prefix?
Tripp
--- Paul DuBois <[EMAIL PROTECTED]> wrote:
At 19:26 -0700 7/7/04, John Mistler wrote:
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?
If you have indexes on column A and column B, then if you create a multiple-column unique index on (A,B), you could remove the index on A. The reason for this is that MySQL can use a leftmost prefix of a multiple-column index as if you had an index on just the leftmost columns.
In other words, an index on (A, B) can be use when you search for combinations of A and B, or when you search for just A.
You cannot remove the index that you have on just B, because B is not a leftmost index of (A, B).
http://dev.mysql.com/doc/mysql/en/Multiple-column_indexes.html
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]
-- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__________________________________ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
