I had to take over a VB program from a person, for a while.
This program was used to enter Student related stuff, here is a breakdown of the tables
Table name CourseInfo
CourseID - Primary key, No dupliactes
CourseName Duplicates OK
Table name StudentAndSubjects
StudentInfo - Primary key, Duplicates OK
SubjectInfo - Prmary key, Duplicates OK
SubjectStartDate - Not indexed
SubjectCompleted - Not indexed
SubjectEndDate - Not indexed
Table name StudentInfo
StudentNo - duplicates OK
FileNo - No duplicates
TimeSlot - duplicates OK
Class - duplicates OK
StartDate - duplicates OK
FullNames - duplicates OK
Surname - duplicates OK
Course - duplicates OK
Monthly - duplicates OK
NoPayments - duplicates OK
CourseID - duplicates OK
StuRecNum - Primary key, no duplicates
Table name SubjectInfo
PK - Primary key, no duplicates
CourseID - duplicates ok
SubjectName - not indexed
StartDate - duplicates ok
EndDate - duplicates ok
These are all the tables. everything works beautifully, and links to where it should, because the program has been running for quite some time.
The only issue is the fact that it allows duplicate student numbers, but not duplicate FileNumbers, in the StudentInfo table. IMHO, it should not allow either, am I right?
I tried changing StudnetNo to no duplicates, but Access gave me some kind of error.
So I set both the FileNo and StudentNo Indexed properties to NO. Access didn't complain.
So, will this affect all existing data, will I lose any info, is it safe to continue?
I'd actually be please to have StudentNo as no Duplicates.
Ah - I understand now. A primary key is (under the hood) enforced using a unique, not-null index. Assuming this is a composite primary key then the index on the first column is a waste of space and can be removed. TBH it looks like the person that built this didn't really know what they were doing and indexed EVERYTHING.
I trust and thank all of you guys' instincts and knowledge. Hopefully one day I'll properly understand databases.
I am attaching the db, so that you can see how all the relationships are set up.
I just don't want it to bomb out when I enter a duplicate file number ( the reason for this is that this app has been running for years, as I understand, and some of the very old records have the same file number as this year's it seems ).
I don't know, I'm open to any suggestions, I just don't want to lose any information, else I'll get the beating of my life