check for repeated data entry in dbase

edkuan

i’ve made a form where admin could register a subject to a student ,
but how do check or made sure that the admin don’t register the same subject twice to the same student . How to make a error warning for this case?

example :
subject_registerd
no | user_id | subject_code
1 0001 PrgC
2 0001 VB101
3 0070 PHP
4 0001 VB101

here user 0001 has double entry of subject code VB101 , how do i code to make sure this wont happen

I’ve created 3 database :
subject_registerd
no | user_id | subject_code

student
no | user_id | user_name | user_login

subjects
no | subject_code | subject_title

I’m using list/menu to select subject to register (i used the insert new record function to insert subject code and the user_id into the subject_registerd dbase )

im using dmx and mysql

hope u could help

Piranha

The best way is to set the values as unique:

CREATE TABLE subject_registerd (
   no int primary key,
   user_id int,
   subject_code varchar(50),

   UNIQUE (user_id, subject_code)
);

That would make it impossible to register the same combination of user_id and usbject_code twice.

But I don't understand your design of the database. You should use the primary keys in the other tables to do the relationship. Like this:

CREATE TABLE subject_registerd (
   no int primary key,   // Note that you don't need the no in this table, you can use no_student and no_subject as a primary key. But it is up you you how you handle it.
   no_student int,
   no_subject int,

   UNIQUE (user_id, subject_code),
   FOREIGN KEY no_student REFERENCES student(no),
   FOREIGN KEY no_subject REFERENCES subject(no)
);

If you don't do it this way it is possible to change the subject code in the subject table without changing the relationships, and it will mess up your database.

edkuan

i'm using phpmyadmin 2.2.3 how do I set foreign key from there ?

edkuan

i'm using phpmyadmin 2.2.3 how do I set foreign key from there ?

if i set subject_code and user_id as uniq that would mean user id 0001 and subject code vb101 can occur only once. But what if user 0001 could register more than one subject example:

subject_registerd
no | user_id | subject_code
1 0001 PrgC
2 0001 VB101
3 0070 PHP
5 0001 PHP

Piranha

If you set it as I showed above with both column names it is the combination of them that are unique. So this would work:

UNIQUE (user_id, subject_code)

If you instead want user_id and subject_code unique for themselfes you should set them in different UNIQUE rows:

UNIQUE (user_id),
UNIQUE (subject_code)

About how to set as foreign key or unique in PHP admin, look in the manual to see how to do it. Or search the web for ALTER TABLE.