foreign key

[linux44]

Ive got problem with defining foreign key
i explain what table ive and what i want
between my Student ,Session there is many to many relation that student session connect them together


student: Format student session: Format
Student ID Autonumber Student ID Autonumber
student name Session ID Autonumber
student address
student dob.....
....



Session: format


Session ID Autonumber
dance days
dance time
no of student


the student id is primery key and session id is primery key ,student session is compound key of this 2 but it wont work it keep saying u can only have one auto number in one table #


what should i do ?

 

[Rabbie]

To model a many to Many relation you need to create 2 1to Many relations. In this case you need a Junction table like TblStudentSession

This would have
3 fields

StudentSessionID(PK)
StudentID(FK) defined as Long to hold a copy of Autonumber PK from Student table
SessionID(FK) defined as Long to hold a copy of Autonumber PK from Session table

Hope this helps

 

[neileg]

I agree with Rabbie. You can apply a compound index set to no duplicates to StudentID(FK) and SessionID(FK) to ensure the combination is unique.

 

[linux44]

StudentSessionID(PK)
StudentID(FK) defined as Long to hold a copy of Autonumber PK from Student table
SessionID(FK) defined as Long to hold a copy of Autonumber PK from Session table





could u please explain a bit more please
thanks

 

[linux44]

i dont understand what do u mean by defining it as long
does it mean the type should be number and format of it long ?

 

[Rabbie]

StudentSessionID(PK)
StudentID(FK) defined as Long to hold a copy of Autonumber PK from Student table
SessionID(FK) defined as Long to hold a copy of Autonumber PK from Session table





could u please explain a bit more please
thanks

our tables should be as follows

Student Table

Student ID Autonumber (PK)
student name
student address
student dob.....
....
etc

Session Table
Session ID Autonumber(PK)
dance days
dance time
etc

StudentSession Junction Table(holds information on which students go to which Sessions)
StudentSessionID(PK)
StudentID(FK) defined as Long to hold a copy of Autonumber PK from Student table
SessionID(FK) defined as Long to hold a copy of Autonumber PK from Session table

Please post what you don't understand about this. Be as Specific as possible

 

[linux44]

thanks for responding
that is the part that i dont understand
(defined as Long to hold a copy of Autonumber PK from Session table)

 

[Rabbie]

thanks for responding
that is the part that i dont understand
(defined as Long to hold a copy of Autonumber PK from Session table)

When you have a 1:many relationship where the 1 side is autonumber you have a field on the many side as a long integer to hold a copy of the 1 side Autonumber. This is known as a Foreign Key. The foreign key should be indexed with duplicates allowed.

i dont understand what do u mean by defining it as long
does it mean the type should be number and format of it long ?

Exactly. That is exactly what was meant.

I hope this has helped you.

 

[linux44]

ok the thing i am saying is differ i have student id as auto number and also session id as autonumber
the problem is when i create table called student session to solve many to many i need student id(fk)ad session id(fk),when i am doing that it give me error it says that in one table i could only have one auto number
what should i do in this situation?