| Author |
Topic |
|
Tiara
Starting Member
6 Posts |
 Posted - 2012-04-26 : 23:15:00
|
| Hi There,I have a question regarding enforce a constraint to my table.There are works on table and a staff table, each staff has specific title(supervisor,authorizer,manager,...). I need to ensure that the supervisor and the authorizer cannot be the same staff on workson table. I am not sure how to do it.Can you please advise me to solve this problem?Thanks |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-04-26 : 23:25:28
|
[code]alter table <table name> add constraint <constraint name> check ( supervisor <> authorizer )[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
Tiara
Starting Member
6 Posts |
Posted - 2012-04-26 : 23:36:19
|
| Thanks for the respond but I don't think is possible to do that because the relationship between these two tables is many to many. I need to enforce constraint to the third table which is workson_staff however I don't have staff title on that table. So how can I use this statement in this case? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-04-27 : 00:33:51
|
oh 2 tables ? maybe you can post the table schema, it will help us to understand the situation better
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Tiara
Starting Member
6 Posts |
Posted - 2012-04-27 : 00:54:33
|
| how can i send an image to the forum? |
|
|
|
Tiara
Starting Member
6 Posts |
Posted - 2012-04-27 : 02:10:35
|
| Here is my table schema http://sqlhelp1.blogspot.com.au/ |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-04-27 : 02:25:13
|
by the looks of it, you probably have to enforce this in your application
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
vinu.vijayan
Posting Yak Master
227 Posts |
Posted - 2012-04-27 : 03:16:59
|
quote:
Originally posted by Tiara
Here is my table schema http://sqlhelp1.blogspot.com.au/
From the above link...I understand that the difference in StaffTyp ie: admin or supervisor is coming because the field "StaffType" in the table "WorksOnStaff" is not an FK referencing the field "StaffType" in the table "Staff".The constraint you need to add here is on the field "StaffType" in the table "WorksOnStaff" by making it a Foreign Key that references the field "StaffType" in the table "Staff".N 28° 33' 11.93148"E 77° 14' 33.66384" |
|
|
|
Tiara
Starting Member
6 Posts |
Posted - 2012-04-27 : 03:29:11
|
| What if, other roles are able to be assigned to more than one particular assignment? For example Mary green can be a supervisor and a sales person of the particular assignment but she cannot be the authorizer for particular assignment? |
|
|
|
vinu.vijayan
Posting Yak Master
227 Posts |
Posted - 2012-04-27 : 05:02:15
|
| So, you are saying that every Staff in the "Staff" table has a "StaffType" but when some Work is carried out then the Staff can be assigned some other "StaffType" in the "WorkStaff" table??Is that what you mean?N 28° 33' 11.93148"E 77° 14' 33.66384" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-27 : 06:54:45
|
| FYI SQLTeam is a Microsoft SQL Server website, and your ERD looks like it's an Oracle database. You'll get better responses to Oracle questions over at http://dbforums.com/. |
|
|
|
|
Enforcing constraints plssss helpp