Defining a "partial" foreign Key..how could I ?

I have a table A, which has 3 fields:

- code, subcode and description (code and subcode are PK)

and a table B which has 2 fields:

- date, code, value (date and code are PK)

So far, no problem. It comes up when I try to define code in table B as foreign key pointing to code in table A, because table A has code and subcode as its PK, not only code...but, I need only code to be in table B. How could I overcome this?

Well this is kind of design issue...

You can't do it directly.. Your table design is wrong.

Try to use the following design:

CodeMaster :: code, date, value , codedescription
Here,
Code is Primary Key

SubCodeMaster :: code, subcode, subcodedescription
Here,
Code references CodeMaster::Code
Code, Subcode is Primary Key

|||

Well, unless the code column is unique in an of itself (thus making it the PK), you can't (and you shouldn't). If it is, you could apply a unique constraint to the code column, but that would be silly.

In all likelihood, you actually need a table for a code itself that relates to both tables. This would be the proper solution, since a code is a different thing than a subcode, and any code info would have to be repeate on each row

You can enforce this with triggers too, but I wouldn't suggest that as the "best" case.