VbzCart/docs/tables/cat items/integrity checks: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (this seems worth a separate page) |
imported>Woozle m (fixed markup tags) |
||
| Line 1: | Line 1: | ||
All of these queries should return with no rows, but there's nothing in the table definition to prevent this from happening. | All of these queries should return with no rows, but there's nothing in the table definition to prevent this from happening. | ||
==duplicate CatNum values== | ==duplicate CatNum values== | ||
<mysql>SELECT | <source lang=mysql>SELECT | ||
CatNum, COUNT(CatNum) AS SameRows | CatNum, COUNT(CatNum) AS SameRows | ||
FROM | FROM | ||
| Line 8: | Line 8: | ||
NOT isPulled | NOT isPulled | ||
GROUP BY CatNum | GROUP BY CatNum | ||
HAVING SameRows > 1</ | HAVING SameRows > 1</source> | ||
The supplier catalog build process will check for duplicates to the catalog numbers it suggests for new items, but items entered manually could still have duplicate catalog numbers. Possibly there should be a unique key consisting of (CatNum,isPulled), but that would also prevent duplicate ''pulled'' and ''dumped'' items, which would be a problem. | The supplier catalog build process will check for duplicates to the catalog numbers it suggests for new items, but items entered manually could still have duplicate catalog numbers. Possibly there should be a unique key consisting of (CatNum,isPulled), but that would also prevent duplicate ''pulled'' and ''dumped'' items, which would be a problem. | ||
==blank CatNum values== | ==blank CatNum values== | ||
<mysql>SELECT | <source lang=mysql>SELECT | ||
* | * | ||
FROM | FROM | ||
cat_items | cat_items | ||
WHERE | WHERE | ||
NOT isPulled AND (CatNum IS NULL)</ | NOT isPulled AND (CatNum IS NULL)</source> | ||
==no Title record== | ==no Title record== | ||
This pulls up active Item records that reference a nonexistent Title record. | This pulls up active Item records that reference a nonexistent Title record. | ||
<mysql>SELECT | <source lang=mysql>SELECT | ||
i.* | i.* | ||
FROM | FROM | ||
| Line 26: | Line 26: | ||
cat_titles AS t ON i.ID_Title = t.ID | cat_titles AS t ON i.ID_Title = t.ID | ||
WHERE | WHERE | ||
(t.ID IS NULL) AND NOT isPulled</ | (t.ID IS NULL) AND NOT isPulled</source> | ||
Revision as of 22:05, 16 July 2019
All of these queries should return with no rows, but there's nothing in the table definition to prevent this from happening.
duplicate CatNum values
SELECT
CatNum, COUNT(CatNum) AS SameRows
FROM
`vbz-vc`.cat_items
WHERE
NOT isPulled
GROUP BY CatNum
HAVING SameRows > 1
The supplier catalog build process will check for duplicates to the catalog numbers it suggests for new items, but items entered manually could still have duplicate catalog numbers. Possibly there should be a unique key consisting of (CatNum,isPulled), but that would also prevent duplicate pulled and dumped items, which would be a problem.
blank CatNum values
SELECT
*
FROM
cat_items
WHERE
NOT isPulled AND (CatNum IS NULL)
no Title record
This pulls up active Item records that reference a nonexistent Title record.
SELECT
i.*
FROM
cat_items AS i
LEFT JOIN
cat_titles AS t ON i.ID_Title = t.ID
WHERE
(t.ID IS NULL) AND NOT isPulled