1

I have two tables on my database and both of them hold some tasks to be done by the user, the first table named taches and the second one admin_taches and both of them contain a column named task_id which is unique in its table

Is there a possibliy for me to make the column task_id unique in both tables ? I need the value of task_id to be unique ! How can i achieve this.

I'm using Oracle WorkBench as IDE for my database

Edit : The id is auto-incremented on insert in each table

Rafik Bari
  • 4,867
  • 18
  • 73
  • 123
  • Can you share some more details about that `task_id`? Where does it come from? Is it an index, or primary key in the database? Is it auto-incremented? Do you set it in your application logic? Can you simply run a check if an id is used in any table before adding another item? – poke Feb 18 '13 at 21:46
  • The fact that you're asking about how to make this unique across both tables should tell you that it's not a good idea. The correct approach is to combine the tables. – Gavin Towey Feb 18 '13 at 23:19

2 Answers2

2

Throw them all in one table, and add a column admin enum('no','yes') default 'no' to it.

Another solution is to prepend one of the ids with a letter, that is not present in the ids.

Bart Friederichs
  • 33,050
  • 15
  • 95
  • 195
  • i think you answer is the easiest workaroud in my case, could you please tell me how to prepend a letter to the second table. for example showing a_1, a_2,a_3... I need the column to be auto incremented. – Rafik Bari Feb 18 '13 at 21:53
  • Don't put it in the table, prepend it wherever you use it. – Bart Friederichs Feb 18 '13 at 21:54
1

You can probably do this by creating a SEQUENCE object. This is a sequence maintained by the DBMS. Then, when you insert into either table, you can use the sequence.nextval

I've used this on databases other than Oracle, but looking at the following page, Oracle seems to support them as well: http://www.techonthenet.com/oracle/sequences.php

Edit: I just realized that you tagged this as "MySql", not Oracle. If you're using MySql, I don;t think this syntax is supported.

Darius X.
  • 2,886
  • 4
  • 24
  • 51
  • I agree with this response, a SEQUENCE object will guarantee that your task_id is unique in both tables. However another approach listed below (one table add a column admin indicator solution), would work if the data set was appropriate between the two tables. – Jordan Feb 18 '13 at 21:55
  • 1
    MySQL doesn't support sequences. – Bart Friederichs Feb 18 '13 at 21:55