Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following three tables in mysql database named "THE_COLLEGE"

mysql> desc students;

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| STU_Id   | char(5)     | NO   | PRI |         |       |
| STU_Name | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)
mysql> desc staff;

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| STF_Id   | char(5)     | NO   | PRI |         |       |
| STF_Name | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)
mysql> desc users;

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| Id         | char(5)     | NO   |     |         |       |
| user_Name  | varchar(20) | YES  |     | NULL    |       |
| Password   | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

3 rows in set (0.02 sec)

If a new row want to be inserted into the table "users", that row must be found in students table or staff table.

Which means a staff or a student can be a user.

Is it possible?

I tried with following foreign keys.

ALTER TABLE users
ADD CONSTRAINT fky_stu
   FOREIGN KEY (ID)
   REFERENCES Students (STU_Id);

ALTER TABLE users
ADD CONSTRAINT fky_stf
   FOREIGN KEY (ID)
   REFERENCES Staff (STF_Id);

But the user table is allow to insert a row, if the row found in both tables (Student and Staff).

But I need to allow if the row found in any of the table (Student or Staff).

share|improve this question
1  
You need one more table (e.g. persons) and all the 3 tables' pks will be referencing it's pk. –  ypercube May 9 '13 at 10:32
    
Use the Party Model –  Neil McGuigan Nov 27 '13 at 1:16
add comment

2 Answers

The comment from @ypercube is right.

You shall model an intermediary table to be able to enforce FKs:

enter image description here

A less formal model, in which FK enforcement would not be possible, could be:

enter image description here

share|improve this answer
add comment

I think you actually want the USERS table to be the parent here, where STAFF and STUDENT are each types of users. Try flipping the fk relationships and doing the inserts in the other order.

share|improve this answer
    
nope. actually the user is an optional. If a student like to use this system, he can register as a user. If a staff like to to use this system, he also can register as a user. But the user is not compulsory. But a user must be exist on students table or staff table. So the student table and the staff table are the parent tables of the user table. –  dinesh.mic May 10 '13 at 4:11
    
I think that you can have the reference to USERS in STAFF and STUDENT but let it be null if they don't use it. –  PhoneixS Apr 4 at 9:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.