Oracle key help needed ASAP

Hi All,

Need some help with keys. I have 2 related tables. One is a rate table.

The other is a Subcode table. The rate table is the parent. It has a letter code, a begin_date, end_date, and a rate. I need to be able to set up rates across several fiscal years. Hence I set this table up with a unique key consisting of the letter_code and the begin_date. The same letters will be used from year to year but the begin_date will make the rows unique. Also the letter_code needs to be used to link to the subcode table. The subcode table has a subcode and a letter_code. The subcode table will have a foreign key on its letter_code. This allows me to pull the correct rate for any given fiscal year with any subcode. These table will also be used with several form screens.

The problem is a follows: I defined the subcode table with the foreign key on it as needed and the rate table with the unique key as needed. I had trouble creating the foreign key using just the letter_code. I then changed my create on the rate table to have the unique key (letter_code and begin_date) but also to have a primary key on the letter_code as well. All seemed to go well until I tried to insert two records in the rate table with the same letter_codes but having different begin_dates. I got the old "unique constraint" error.

Any help would be greatly appreciated.

Send me E-mail if you have time and post for all to see as well.

Thanks again for any help.

Jim Jones Received on Tue Mar 25 1997 - 00:00:00 CST