Can ORACLE do this?

How do you check data integrity when two columns are related but not in the same table?

An example would be having a system which keeps track of automobile inventory. In one table (Master) you have the serial number, manufacturer, etc. In another table (Detail) you have the serial number (Foreign Key), color, etc. Each manufacturer has its own list of colors that it uses. So when a serial number and color is entered, you need to verify that the color is allowed for the manufacturer of the automobile. My idea was to pass the serial number to a stored procedure that finds the manufacturer (from the header) and passes back a list of valid colors.

The check constraint would look like:

CHECK (New.Color IN

          (SELECT Color FROM StoredProcedure(New.SerialNumber))

Is this allowed in ORACLE? The database we are currently using allows this to compile but doesn't work.

If not, how would you accomplish this type of check?

Thanks in Advance

Todd

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Aug 31 1999 - 09:40:55 CDT