Hi,
I have one main table which has about a half dozen foreign keys to
other tables to keep the data normalised, ie when a particular VARCHAR
value is inserted into the main table, I want to check the referenced
table for that value (which could be quite a lengthy string, and which
could be repeated in the main table many times), and if it exists,
only store the integer primary key for that value from that referenced
table in the main table. This is all fairly straightforward, but how
do I handle the fact that even though I want the insert query to pass
in a VARCHAR for a particular field in the main table, I want an
integer to eventually be inserted into that field (ie the integer
which corresponds to that VARCHAR in the foreign table). Can I insert
a VARCHAR into an integer field, so long as my BEFORE INSERT trigger
does the necessary work to locate the correct integer and use that
instead? I'd like my clients to be able to pass in strings via the
INSERT statement, but only end up storing integers in the table
itself.
Thanks,
Shannon