Re: How to write a constraint which need to check other table?

["A. Kretschmer" <andreas.kretschmer@xxxxxxxxxxxxxx>]

In response to ????????? :
> create table a(
>          name varchar(32);
> );
> 
> create talbe b(
>           name1 varchar(32);
>           name2 varchar(32);
> );
> 
> 
> How to write a constraint to check name1, name2 in the table a without change
> table defination?

-- Okay, your tables without constraints:


test=# create table a (name char(32));
CREATE TABLE
test=*# create table b (name1 char(32), name2 char(32));
CREATE TABLE


-- Now add a primary key to table a:

test=*# alter table a add primary key (name);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"a_pkey" for table "a"
ALTER TABLE

-- And now adds two foreign keys to table b:


test=*# alter table b add foreign key (name1) references a;
ALTER TABLE
test=*# alter table b add foreign key (name2) references a;
ALTER TABLE

-- show the tables:

test=*# \d a;
          Table "public.a"
 Column |     Type      | Modifiers
--------+---------------+-----------
 name   | character(32) | not null
Indexes:
    "a_pkey" PRIMARY KEY, btree (name)
Referenced by:
    TABLE "b" CONSTRAINT "b_name1_fkey" FOREIGN KEY (name1) REFERENCES a(name)
    TABLE "b" CONSTRAINT "b_name2_fkey" FOREIGN KEY (name2) REFERENCES a(name)

test=*# \d b;
          Table "public.b"
 Column |     Type      | Modifiers
--------+---------------+-----------
 name1  | character(32) |
 name2  | character(32) |
Foreign-key constraints:
    "b_name1_fkey" FOREIGN KEY (name1) REFERENCES a(name)
    "b_name2_fkey" FOREIGN KEY (name2) REFERENCES a(name)


Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general