From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to write a constraint which need to check other table? |
Date: | 2009-09-28 09:46:10 |
Message-ID: | 20090928094610.GD10298@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Vasiliy G Tolstov | 2009-09-28 10:33:36 | postgresql error |
Previous Message | 纪晓曦 | 2009-09-28 09:18:48 | How to write a constraint which need to check other table? |