Re: INDEX

[SGreen]

Scott Hamm <linuxgold@stripped> wrote on 09/26/2005 02:21:38 PM:

> > On 9/26/05, SGreen@stripped <SGreen@stripped> wrote:
> > 
> > 
> > Scott Hamm <linuxgold@stripped> wrote on 09/26/2005 01:59:52 PM:
> > 
> > > How do I set Index to enforce that ONLY 1 QAID can own that order 
number,
> > > but nothing else?
> > > 
> > > For example:
> > > 
> > > QAID [order] ErrorType
> > > 11223 123456789 19 'ALLOWED TO HAVE different ErrorTypes as long as 
one QAID
> > > number uses it.
> > > 11223 123456789 19 'VIOLATED cannot have same ErrorTypes -> UNIQUE 
INDEX
> > > (QAID,[order],ErrorType)
> > > 11223 123456789 15
> > > 11223 123456789 NULL
> > > 
> > > 11240 123456789 14 'VIOLATED -- order was owned by QAID 11223, 
therefore can
> > > not used by different QAID
> > > 
> > 
> > Please post the output from SHOW CREATE TABLE. That way I can see 
> > not only what your columns are actually called, I can also see what 
> > other keys have been defined on the table. 
> > 
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine 
> > 

<Scott's original table def, reformatted>
CREATE TABLE `qaerrors` ( 
        `QAID` int(10) default NULL
        , `ErrorTypeID` int(10) default NULL
        , `Order` varchar(9) default NULL
        , `ID` int(10) NOT NULL default '0'
        , PRIMARY KEY  (`ID`)
        , UNIQUE KEY `Index_2` (`Order`,`ErrorTypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I am not sure that any DB can do what you want with just one table. Here's 
why. There are only 7 possible combinations of UNIQUE indexes you can 
create by using 3 columns from the same table. 3 of them are each column 
individually (which obviously won't work as a solution). One you listed, 
that leaves just these two to consider.



With a UNIQUE(`QAID`,`Order`), you will be limited to only one row with 
the same (`QAID`,`Order`) pair. This will not work as you said that there 
can be multiples of a pair so long as each instance of a pair is matched 
with a different ErrorTypeID. It would prevent this:

11223 123456789 19
11223 123456789 15

because the same (`QAID`,`Order`) pair would appear twice. Not what you 
wanted.


With UNIQUE(`QAID`, `ErrorTypeID`), the following pair of records would be 
allowed:
11223 123456789 19
11240 123456789 14

In this set of data the same `Order` is now assigned two different QAID 
values. Also what you didn't want.

the last combination: UNIQUE(`QAID`,`Order`,`ErrorTypeID`), each triplet 
can only appear once but that still doesn't prevent the case of 

11223 123456789 19
11240 123456789 14


So, indexes alone can't work. However, I believe a Foreign Key will do the 
trick. First, we need to create a table to hold the "ownership" 
information for any `Order` value. The UNIQUE index will prevent any 
`Order` value from being listed more than once which means that there can 
only be one possible `QAID` value for any `Order` value on this table. 

CREATE TABLE qaerrowner (
        `QAID` int(10) default NULL
        , `Order` varchar(9) default NULL
        , UNIQUE (`Order`)
        , KEY(`QAID`,`Order`)
)ENGINE=InnoDB;

Now, we need to slap a constraint on `qaerrors` so that it is compelled to 
use only (`QAID`, `Order`) pairs that exist in qaerrowner:

ALTER TABLE qaerrors ADD KEY (`QAID`,`Order`)
, ADD CONSTRAINT FOREIGN KEY (`QAID`,`Order`) REFERENCES 
qaerrowner(`QAID`, `Order`);

(According to 
http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html, 
this is valid syntax. However I don't have the time to test it with some 
live data. Now you see why I added the extra KEY() to qaerrowner. If I 
hadn't this definition would have failed.)

The drawback is, you have to write to two tables whenever you want to 
create a `qaerrors` record. Once to identify the owner (use an INSERT 
IGNORE to `qaerrowner`) and a second time to log the actual error (another 
INSERT IGNORE, this time to `qaerrors`). Check the number of rows affected 
to determine if the record made it in or not. If you didn't affect any 
records, it was blocked by the FK.

I know this may seem a bit convoluted but this is exactly the situation 
that FKs were developed to enforce.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine