On Tue, 1 May 2001 [EMAIL PROTECTED] wrote:
> I am using perl DBI to access an sql server database.
> One table has component part numbers that have failed, keyed in by
> operators. I need to read in and count these which is no problem but I also
> need to compare them with an approved parts table to make sure that they are
> valid part numbers. Currently I am doing this by reading the failed parts
> into a hash and then using the hash keys as placeholders to query the parts
> valid table.
> Is it possible with DBI to do something like
> SELECT table1.failed_parts FROM table1 WHERE table1.failed_parts =
> table2.valid_parts
You can do this, it's pretty basic SQL, but the problem is that you have
no way of enforcing this 'referential integrity' rule.
The correct way to do this is to use a foreign key constraint in your
failed parts table. This enforces the rule that a failed part number
cannot be entered into the database unless that part number exists in the
approved parts table. Some databases (like MySQL) don't support foreign
keys, but all of the commercial ones do, as well as PostgreSQL.
You would have something like this:
failed_part integer references parts(part_num)
in your table creation statement. There are more options for creating
foreign key constraints, refer to your server documentation for the
specifics. They can make your life a lot easier.
-- Brett
http://www.chapelperilous.net/btfwk/
------------------------------------------------------------------------
Hey, Jim, it's me, Susie Lillis from the laundromat. You said you were
gonna call and it's been two weeks. What's wrong, you lose my number?
