12
I have 2 tables.
Project and task
Project_Table:
prjct_id int, PK(identity Index)
prjct_name nvarchar(50),(unique index)
prjct_position int,(unique Index)
Task_table:
tsk_id int, PK
prjct_id int, (From Project_table)
tsk_name nvarchar(50),(unique Index)
tsk_position int
Say for eg., there are 5 projects, and each project consists of 10 - 12 tasks. Each Project have their positions(buttons) in a form(VB.NET). while clicking on a particular Project Buttons, This should go to respective tasks. (This is my Idea)
prjct_id in the task_table is not an unique index, because there is a necessity to enter 2 or more tasks in the table with the same project.
how can i create an unique index with the tsk_position which allows only index features within one project?
eg. project_table:
----prjct_id----prjct_name----prjct_position
-------1----------a1a1-----------101
-------2----------b2b2-----------121
task_table:
----tsk_id-----project_id------tsk_name-----tsk_position
------1-----------1--------------aaa------------1
------2-----------1--------------bbb------------2
------3-----------2--------------xxx------------1
------4-----------2--------------yyy------------2
------5-----------1--------------ccc------------3
------6 ----------1--------------ddd------------4
project_id in the task_table relates with the project_table.
There are 4 tasks for project - a1a1 with task_id - 1,2,5,6 and task_position - 1,2,3,4 respectively
There are 2 tasks for project - b2b2 with task_id - 4,5 and task_position - 1,2 respectively.
task_table:
----tsk_id-----project_id------tsk_name-----tsk_position
------7-----------2--------------zzz------------2
------8-----------1--------------eee------------3
(this should be wrong)
Because there is already a task in task_position - 2 for project (b2b2) which holds the project_id 2.
and the same case for the project 1 (a1a1) there is already a task in the position 3.
So could anyone help me out with this avoiding such type of duplications in SQL?
