Обсуждение: How to write a constraint which need to check other table?
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?
ALTER TABLE b ADD CHECK( ??? );
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?
ALTER TABLE b ADD CHECK( ??? );
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>
In response to ????????? :> Yes, you are right. That maybe a bad example. what I want to say maybe likePlease answer to the list, okay?
> 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?
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)