MySQL ODBC Connector

Author

INDEX

Scott Hamm

2005-09-26, 1:23 pm

------=_Part_521_7993878.1127757592107
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 QAI=
D
number uses it.
11223 123456789 19 'VIOLATED cannot have same ErrorTypes -> UNIQUE INDEX
(QAID,& #91;order],ErrorType
)
11223 123456789 15
11223 123456789 NULL

11240 123456789 14 'VIOLATED -- order was owned by QAID 11223, therefore ca=
n
not used by different QAID

--
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

------=_Part_521_7993878.1127757592107--

SGreen@unimin.com

2005-09-26, 1:23 pm

--=_alternative 0063B1F085257088_=
Content-Type: text/plain; charset="US-ASCII"

Scott Hamm <linuxgold@gmail.com> 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,& #91;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
--=_alternative 0063B1F085257088_=--

SGreen@unimin.com

2005-09-26, 8:24 pm

--=_alternative 0071BD1185257088_=
Content-Type: text/plain; charset="US-ASCII"

Scott Hamm <linuxgold@gmail.com> wrote on 09/26/2005 02:21:38 PM:
[color=darkred]
number,[color=darkre
d]
one QAID[color=darkred]
INDEX[color=darkred]

therefore can[color=darkred]

<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`,`ErrorTypeI
D`)
) 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/i...nstraints.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

--=_alternative 0071BD1185257088_=--

Sponsored Links