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

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)

In response to ????????? :

Yes, you are right. That maybe a bad example. what I want to say maybe like
this:

create table a (
�������� id integer,
�������� room varchar(32),
�������� start time,
�������� end time,
�������� PRIMARY KEY(id)
)
How can I check if it is the same room, when I insert the data, the start time
and end time doesn't overlap?

Please answer to the list, okay?

test=*# create table a ( id int primary key, room char(32), start_time timestamp, end_time timestamp);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"
CREATE TABLE
test=*# create or replace function check_overlapp() returns trigger as $$begin perform id from a where room=new.room and (start_time,end_time) overlaps (new.start_time,new.end_time); if found then raise notice 'room reserved'; return null; else return new; end if; end; $$language plpgsql;
CREATE FUNCTION
test=*# create trigger trg_check before insert or update on a for each row execute procedure check_overlapp();
CREATE TRIGGER
test=*# insert into a values (1, 'room1', '2009-10-01'::timestamptz, '2009-10-10'::timestamptz);
INSERT 0 1
test=*# insert into a values (2, 'room1', '2009-09-01'::timestamptz, '2009-09-10'::timestamptz);
INSERT 0 1
test=*# insert into a values (3, 'room1', '2009-09-08'::timestamptz, '2009-09-20'::timestamptz);
NOTICE: room reserved
INSERT 0 0

You can also use RAISE EXCEPTION to force an error:

test=*# create or replace function check_overlapp() returns trigger as $$begin perform id from a where room=new.room and (start_time,end_time) overlaps (new.start_time,new.end_time); if found then raise exception 'room reserved'; return null; else return new; end if; end; $$language plpgsql;
CREATE FUNCTION
test=*# insert into a values (3, 'room1', '2009-09-08'::timestamptz, '2009-09-20'::timestamptz);
ERROR: room reserved
test=!#

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

Thank you very much, I think I need to stady more about trigger.

2009/9/28 A. Kretschmer <andreas.kretschmer@schollglas.com>