Re: unique constraint on more than one tables

First of all explicitly inserting IDs into your serial column sounds
like a bad idea (in the general case).

Unfortunately, I don't think inheritance can help you with this. Key
quote from the docs: "A serious limitation of the inheritance feature is
that indexes (including unique constraints) and foreign key constraints
only apply to single tables, not to their inheritance children."

So, you can create some triggers. Maybe something like this (untested):

create or replace function tf_nodupes()
  returns "trigger" as
$body$
begin
  if new.id in (select id from bla_a union select id from bla_b)
  then
    raise exception 'a suitable message about dupes goes here';
  end if;
  return new;
end;
$body$
language 'plpgsql';

create trigger t_nodupes
  before insert on bla_a
  for each row
  execute procedure tf_nodupes();

create trigger t_nodupes
  before insert on bla_b
  for each row
  execute procedure tf_nodupes();



> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Akbar
> Sent: Thursday, December 28, 2006 4:01 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] unique constraint on more than one tables
>
> Hi,
>
> Imagine I have two tables, like this:
>
> create table bla_a (
>   id serial primary key,
>   name varchar(31) not null,
>   comment varchar(31)
> );
>
> create table bla_b (
>   id serial primary key,
>   name varchar(31) not null,
>   blabla int
> );
>
> I want to make sure that both tables could not have the same value for
> name column. Can I do that?
>
> insert into bla_a ( id, name, comment ) values ( 1, 'bo', 'ha');
> insert into bla_b ( id, name, comment ) values ( 1, 'bo', 3);
>
> I want to make the second insertion failed because of unique
> constraint. Can I do that?
>