Constrain a transaction table using data from a master table

• Caruncles

  Hall of Fame

  Points: 3299

  More actions

  August 19, 2021 at 8:13 pm

  #3921868

   

  I have a "Master" product table that looks like this: table name tWholesaleProduct

  

  Then we have a transaction table that records the sales and receipts.

  

  Here's the rub.  Most of these receipt transactions are done with handheld scanners.  I'm getting a lot of junk for item numbers in the transaction table for items that do not exist in the master product table.  I want to somehow constrain the transaction table to not accept items which are not in the master table.  SQL constraints will not allow a query, so how can this be done?

   

  Wallace Houston
  Sunnyland Farms, Inc.

  "We must endeavor to persevere."

• ratbak

  Ten Centuries

  Points: 1358

  More actions

  August 19, 2021 at 10:05 pm

  #3921907

  This is precisely what foreign keys are for.

  Is ItemNumber in the transaction table equivalent to the ProductId in the master table?