Composite Key with a column that auto increments and reset when foreign key value changes
Automatic?

Unknown.  Most likely, not. (as in: no out-of-the-box database features)

Manual?

Yes.

You need to keep track of "last number used" in a table. eg

create table raf (
  raf_id        int, -- identity/autoincrement
  last_number   int default 4000 not null,
  constraint raf_pk primary key (raf_id)
);


From there, your process needs to go like this:


grab a lock for RAF_ID
"calculate" the next number.
Update the RAF table.
Use that value in the other table
COMMIT the data
Release the lock.


Depending on your RDBMS, some steps can be combined into a single step.  The HOWTO do these things (eg Best practice for "grab a lock") can also be RDBMS dependent.

Notes

The "grab a lock" step will serialize your code.  This is bad for databases.  This requirement/process will limit how fast you can add new rows.  I suggest you look at alternative methods (eg a VIEW that uses RANK() ).

Can this be implemented in a TRIGGER?  Maybe.