Scott Hamm <[EMAIL PROTECTED]> wrote on 09/26/2005 02:21:38 PM:
> > On 9/26/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> >
> > Scott Hamm <[EMAIL PROTECTED]> 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
