Foreign key as primary key?

I currently have 2 tables STUDENTS and SUBJECTS. I need to create another table for ENROLLMENTS, the thing is that in that table I need to know the minimal attendance, which is both dependent on student (if he has a job it will be lower) and on subject (each subject has a base minimal attendance). Should I create another table like MINIMAL_ATTENDACE which holds the base minimal attendance of each Subject ? If so, is it good to have the primary key in that table be also the primary key from SUBJECTS ?

For example I have :

--STUDENTS--

ID  NAME  HIRED

1   Paul    1


--SUBJECTS--

ID  NAME  NUMBER_OF_TESTS  M_ATTENDANCE

1   Math        2              10


--ENROLLMENTS--

ID  STUDID  SUBID   M_ATTENDANCE
1      1       1        7 (Because Student is hired)

Answer

You can use a several constraints on a single column. Apparently, there is a many-to-many relationship between STUDENT and SUBJECT. A possible approach for implementing this (with SQL) is an “intersection” or “bridge” table is. You can use a composite PK, and foreign key constraints like this:

Demo (dbfiddle)

-- parent tables (and test data)
create table students (id primary key, name, hired)
as
select 1, 'Paul', 1 from dual union all
select 2, 'Maggie', 1 from dual union all
select 3, 'Herbert', 0 from dual ;

create table subjects (id primary key, name, number_of_tests, attendance_standard, attendance_hired)
as
select 100, 'Maths', 2, 10, 7 from dual union all
select 200, 'Biology', 3, 12, 8 from dual union all
select 300, 'Physics' 2, 10, 8 from dual ;

-- intersection
create table enrollments(
  studentid number references students( id )
, subjectid number references subjects( id )
, constraint enrollments_pk primary key( studentid, subjectid )
) ;

-- test data for the "intersection" table: 
-- every student needs to complete every subject
insert into enrollments
select S.id, SUB.id
from students S cross join subjects SUB ;

In the example (above), we assume that {1} the “hired” attribute belongs to a STUDENT, and {2} the different “attendance” attributes are attributes of SUBJECT.

You can now just join the 3 tables, and find the required attendance for each student using the query below.

-- Get the attendance requirements for each student & their chosen subject
select 
  S.name, S.hired
, SUB.name
, case 
    when S.hired = 1 then SUB.attendance_hired
    else SUB.attendance_standard
  end as attendance_required
from students S
  join enrollments E on S.id = E.studentid
  join subjects SUB  on SUB.id = E.subjectid 
;

-- output
NAME    HIRED   NAME    ATTENDANCE_REQUIRED
Paul    1       Maths   7
Maggie  1       Maths   7
Herbert 0       Maths   10
Paul    1       Biology 8
Maggie  1       Biology 8
Herbert 0       Biology 12
Paul    1       Physics 8
Maggie  1       Physics 8
Herbert 0       Physics 10

If the “hire” status of a STUDENT can change during an academic year (or semester), you’d probably need to add some more entities (and subsequently: tables) for keeping track of each STUDENT’s hire state change.