How about generating of IDs from a one sequence?

below I highlighted key points of the question

"How can i enforce unique key across multiple tables. Table1 and Table2 both have ID primary key column. Is it possible to restrict, while inserting and updating into these tables, the union of ID values from two tables are unique."

You can do this...

May be you can create third table. Create unique index on that table... and on both the tables, create row level trigger to populate third table. Unique index enforced on third table will restrict user entering duplicate values on first two tables.

>> maybe we can create that third table
>> and drop the first two
>> and if needed create views that give the perception that >> there are two tables...

Perhaps even making it a 2 partition table with views that point to each partition, if we "must" have 2 tables for performance reasons.

OMFG

I have, on *rare* occasion done something along these lines:

create type typbase as object (...)
  not final not instantiable;
create type typ1 under typbase (...)
  final instantiable;
create type typ2 under typbase (...)
  final instantiable;
create table t1 (
  pk  number primary key,
  tp  typbase )
/

-- didn't check the syntax on any of that, but you get the picture. This allows us to put typ1 or typ2 in the t1 table. Sort of a way to have two different things with one primary key.

I'm not a big proponent of this approach (I like scalar only when I can), but I have on occasion used this method to fulfill a need. YMMV.

Maybe what the O.P. started with was a table such as

create table table1
  id number primary key
  <1st set of attributes>
  <2nd set of attributes>

and then was compelled to add a second table such as

create table table2
  id number primary key
  <same 1st set of attributes as table1>
  <unique 2nd set of attributes>

In which case the O.P. really wants to do something like this:

create table t_p
( id number primary key,
  <common attributes>

create table table1
  id number primary key
  <attributes of "table1">
  constraint id 
    foreign key (id)
    references t_p(id)

create table table2
  id number primary key
  <attributes of "table2">
  constraint id 
    foreign key (id)
    references t_p(id)
)
/

[but I fear we are trying to help him find a better way to feed his Zebra, without bothering to find out if he really has a horse]

This is one thread that went from unclear to confusing.

I don't understand the flawed two table approach and it sounds as a rule of thumb really.

What I know is that super-type/sub-type relationships (generalizations) do occur in data modeling and the choice of implementing them can lead to:
1. One table with all the attributes
2. One table for each sub-type, each having the super-type attributes plus its own sub-type attributes
3. One table for the super-type and one table for each sub-type, each with its own attributes.

Here is an example: Employee as the super-type with Consultant and Full-Time Employee as exclusive sub-types. I might want to have relationships to Employee for something like Address, relationships to Full-Time Employee for something like Benefit and relationships to Consultant for something like External Vendors.

The choice of which to implement depends on many factors ... to suggest [or lead one to belive] #1 is always OK (lots of Nulls is always good !?) doesn't seem quite right.

There is no need to specifically enforce unique key across multiple [the sub-type] tables but surely one can rely on that being the case.

create table p
( id number primary key
 ,nm varchar2(10) not null
);

create table s1
( id number primary key references p on delete cascade
 ,nm varchar2(10) not null
);

create table s2
( id number primary key references p on delete cascade
 ,nm varchar2(10) not null
);

create or replace trigger aiu_s1
after update or insert on s1 for each row
declare
  n number;
begin
  select count(*) into n from p where id = :new.id;
  if n = 0 then
    raise_application_error(-20000,'Cannot insert/update s1 because p does not exist');
  end if;
  select count(*) into n from s2 where id = :new.id;
  if n = 1 then
    raise_application_error(-20000,'Cannot insert/update s1 because s2 does exist');
  end if;
end;
/
show errors

create or replace trigger aiu_s2
after update or insert on s2 for each row
declare
  n number;
begin
  select count(*) into n from p where id = :new.id;
  if n = 0 then
    raise_application_error(-20000,'Cannot insert/update s2 because p does not exist');
  end if;
  select count(*) into n from s1 where id = :new.id;
  if n = 1 then
    raise_application_error(-20000,'Cannot insert/update s2 because s1 does exist');
  end if;
end;
/
show errors

create or replace view psub as
select s1.id
      ,s1.nm  s1_nm
      ,cast (null as varchar2(10) ) s2_nm
from  s1
union all
select s2.id
      ,cast (null as varchar2(10) ) s1_nm
      ,s2.nm  s2_nm
from  s2
;

alter view psub add constraint psub_pk primary key (id) disable novalidate;

Two tables sharing, in exclusive mode, the primary key values are not necessarily the result of a flawed approach. Could be ... but it is not a certainty. Just my point of view.

<How can i enforce unique key across multiple tables. Table1 and Table2 both have ID primary key column. Is it possible to restrict, while inserting and updating into these tables, the union of ID values from two tables are unique.

This is actually pretty simple. Use two different sequences. Have one start with an odd number, the other start with an even number. Set the "increment by" to 2. You won't have any collisions between the values generated by the sequences.

If you want to do this for THREE tables, user three sequences, start with 1, 2 or 3 and increment by 3.

If you want to do this for FOUR tables...

Well, hopefully you get the idea.

(Actually came up with this as a fix in Oracle applications some years ago where two different sequences were being used to generate keys on the same table. They started out with values very far apart, but eventually converged and caused problems.)

Jeff:

Just by using sequences, uniqueness is not enforced ... one update and uniqueness is gone without any complaint from the database.

But, why is this about synthetic keys anyway? That's a non-issue: use one sequence for all the synthetic PKs in the system, disable updates on all such PKs (since updating something of no meaning has ... no meaning) and you're done! What would be the purpose though? Now any "Brand" row has a different meaningless PK value than any row in "Address", "Device", etc.

Assume the IDs are supplied by the end-users.

Tom,

I rushed that example ... obviously there is a need to serialize on the parent row. Sorry about that.

Silly Billy.

As I read the request, he was looking for no conflict in PKs between tables. You're not supposed to update PKs. And even if you do, presumably you would update via the PK generating sequence, not by user input, so having the sequences set as I described would still prevent collisions.

Don't do it, but here's how you can do it:

18:25:41 ORCL> create table table1 (pk number);

Table created.

18:25:41 ORCL> create table table2 (pk number);

Table created.

18:25:41 ORCL>
18:25:41 ORCL> create or replace function unionpk(pkarg number) return number deterministic is
18:25:41   2  pkvar number;
18:25:41   3  begin
18:25:41   4  select pk into pkvar from table1 where pk=pkarg
18:25:41   5  union all
18:25:41   6  select pk from table2 where pk=pkarg;
18:25:41   7  return pkvar;
18:25:41   8
18:25:41   9  exception when too_many_rows then raise dup_val_on_index;
18:25:41  10  end;
18:25:41  11  /

Function created.

18:25:41 ORCL>
18:25:41 ORCL> create unique index table1pk on table1(unionpk(pk));

Index created.

18:25:41 ORCL> create unique index table2pk on table2(unionpk(pk));

Index created.

18:25:42 ORCL>
18:25:42 ORCL> insert into table1 values (1);

1 row created.

18:25:42 ORCL> insert into table2 values (1);
insert into table2 values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (.) violated
ORA-06512: at "SYS.UNIONPK", line 9
ORA-01422: exact fetch returns more than requested number of rows

Again: rethink your design... Tom's suggestion of merging the two tables and then have a view for each of the old ones was at the same time fun and 100% right!

You can use before insert trigger in both tables.

Create PK in both tables and

While insert into first table check in second table, while inserting into second table check in first table.

Narasimha

This restriction ought to be enforceable by using an on commit fast refresh materialized view, with a union all of the required columns from the tables plus a marker column to indicate the source table, and a unique constraint on the required columns.

The proof being left as an exercise for the reader :)

Since someone has mentioned triggers again, I might as well correct my silly example and provide something which has a chance of actually working:

create or replace trigger aiu_s1
after update or insert on s1 for each row
declare
  n number;
begin
  select id into n from p where id = :new.id for update nowait;

  select count(*) into n from s2 where id = :new.id;
  if n = 1 then
    raise_application_error(-20000,'Cannot insert/update s1 because s2 does exist');
  end if;
end;
/

create or replace trigger aiu_s2
after update or insert on s2 for each row
declare
  n number;
begin
  select id into n from p where id = :new.id for update nowait;

  select count(*) into n from s1 where id = :new.id;
  if n = 1 then
    raise_application_error(-20000,'Cannot insert/update s2 because s1 does exist');
  end if;
end;
/

... which still leaves open the possibility of deadlocks occurring.

Of course, if this is about a modeling error then that should be fixed rather than looking for workarounds.

Cheers.

Hi Tom,
I have a problem regarding enforcing a unique check. I have the following table
create table t
(t1 number,
t2 VARCHAR2(15) CHECK(t2 IN('PENDING','ACTIVE','INACTIVE','SUSPENDED','DELETED','PENALTY'))
)
I need to enforce a check such that for the same values of t1 combinations of 'PENDING','ACTIVE','INACTIVE','SUSPENDED','PENALTY' is not allowed
but for the same value of t1 multiple values of DELETE is allowed

eg:
CONSIDER THE FOLLOWING ENTRIES IN THIS TABLE
1,'ACTIVE'
1,'DELETED'
1,'DELETED'--ALLOWED
1,'ACTIVE'--NOT ALLOWED

create unique index on t
( case when t2 = 'DELETED' then null else t1 end,
  case when t2 = 'DELETED' then null else t2 end 
)
/

Using GUIDs should ensure uniqueness across tables if you want to use 2 tables. I'm not saying that you should use 1 or 2 tables though.

Some people recommend that GUIDs are used as a rule (in case one day you need them). My opinion is you use them only when you have to. Tom, what is your opinion on using GUIDs for primary keys in general?

Tom answers that on another thread ... ;)

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1249800833250

THANKS GREG for that prompt followup!!!

There is one more possible solution. Create an updateable view using the two tables. The users should be allowed to insert/update rows using only the view.

We can then use instead of trigger on that view to enforce required integrity. This will avoid creation of third table.

Hi,

I see this question more as an academic issue - however to ENFORCE unique key across multiple tables you can do this:

create table t_1(
  the_key number primary key, 
  some_data varchar2(100)
)
/
alter table t_1 add constraint t2_global_unique check (the_key > 0)
/

create table t_2(
  the_key number primary key, 
  some_data varchar2(100))
/
alter table t_2 add constraint t2_global_unique check (the_key < 0)
/

Jan

I agree with Tom, it either should be one table or 3 tables (one master table). So, as others said I treat this as an academical question. Here is a way to generate either the "one big table" or the master table :

CREATE TABLE x1 (id NUMBER PRIMARY KEY, a VARCHAR2(10), b NUMBER)
/

CREATE TABLE x2 (id NUMBER PRIMARY KEY, a VARCHAR2(10), c DATE)
/

CREATE MATERIALIZED VIEW LOG ON x1 WITH ROWID
/

CREATE MATERIALIZED VIEW LOG ON x2 WITH ROWID
/

CREATE MATERIALIZED VIEW x
REFRESH FAST ON COMMIT
AS
SELECT id, a, b, NULL AS c, ROWID AS row_id, 'X1' AS union_marker
FROM x1
UNION ALL
SELECT id, a, NULL AS b, c, ROWID AS row_id, 'X2' AS union_marker
FROM x2
/

ALTER TABLE x
ADD CONSTRAINT x_id_uk UNIQUE (id)
/

If you only need the master table, leave only the common attributes in the materialized view. If you are sticking with the original question ("just wanna make the id unique!"), then leave only the id column. (Is it possible to make it index organized? I think it can't be done with rowid mvs and you can do union all with rowid mvs only.)

Perhaps I'm missing something here and I realize this is a three-year old thread, but I'd like to ask this same question but with regards to natural, non-surrogate primary key columns.

I have this same problem, but not with the sequence-generated primary key columns -- with natural keys. Here is a simple example.

Say I have a parent table called "PARENT" which presents an adult who has children. And I have a "CHILD" table which represents the children of the adults in the "PARENT" table, a typical "parent-child" relationship set up between the two. Now, let's say I have a restriction that the first names of each parent-child pair must be unique. I can have a parent named "Bob" with three children: Jim, Sally, and Paul. I could have another parent named "Bob", but that parent is not allowed to have any children named Jim, Sally, or Paul, or the name of any children of any other "Bob" in the system. And I could certainly have any other number of children named "Jim", but none of them can have a parent named "Bob" or any other parent's name of any other "Jim" in the "CHILD" table.

However impractical that example might sound, is there a way to maintain uniqueness at the database level in this example? Perhaps I'm missing something obvious and just need to be beaten over the head.

Thanks for the insight.
Shane.

I have the following 2 tables:

create table T1( 
id number(10) primary key,
agentId number(10),
deptId number(10),
CONSTRAINT "UQ_T1" UNIQUE (agentId, deptId)
)

create table T2(
T1_id number(10) references T1(id),
market_name varchar2(100)
)

These tables are joined via T1.id in table T2 that holds the market names corresponding to T1(1:n relationship). Now, i need to enforce another constraint that agentId and market_name combination has to be unique.

If i combine the tables into a single table, i cannot enforce <agentId, deptId> uniqueness.

Can you help me out how to enforce the same.

Thanks in advance for your help.

create table T1( 
id number(10) primary key,
agentId number(10),
deptId number(10),
CONSTRAINT "UQ_T1" UNIQUE (agentId, deptId),
UNIQUE (agentId, id)
);

create table T2(
T1_id number(10) references T1(id),
agent_id number(10), 
market_name varchar2(100),
unique ( agent_id, market_name ),
foreign key ( t1_id, agent_id ) references t1 ( id, agentid )
);

insert into t1 values (1, 1, 1);

insert into t2 values (1, 1, 'market 1');
insert into t2 values (1, 1, 'market 2');
insert into t2 values (1, 1, 'market 1');

ORA-00001: unique constraint (CHRIS.SYS_C0010685) violated