Thread: SQL CONSTRAINTS - Constraining time values from two attributes on a table.
SQL CONSTRAINTS - Constraining time values from two attributes on a table.
From
"Maher, Christopher W"
Date:
Hi, I was looking for a solution on how to write a constraint into a ' create table ' expression that would ensure that one ' TIME ' attribute value called arrival_time (declared as TIME) is allways constrained to have a value that ensures it is allways after another attribute value called departure_time (declared as TIME). Any know how this constraint would be written?? Any help on this would be much appreciated, Thanks, Chris.
Re: SQL CONSTRAINTS - Constraining time values from two attributes on a table.
From
"Josh Berkus"
Date:
Chris,
> Hi,
> I was looking for a solution on how to write a constraint into a '
> create
> table ' expression that would ensure that one ' TIME ' attribute
> value
> called arrival_time (declared as TIME) is allways constrained to have
> a
> value that ensures it is allways after another attribute value called
> departure_time (declared as TIME).
> Any know how this constraint would be written??
Easy:
CONSTRAINT arr_dep_time CHECK ( arrival_time > departure_time )
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Attachment
Maher, Christopher W writes:
> I was looking for a solution on how to write a constraint into a ' create
> table ' expression that would ensure that one ' TIME ' attribute value
> called arrival_time (declared as TIME) is allways constrained to have a
> value that ensures it is allways after another attribute value called
> departure_time (declared as TIME).
create table test ( arrival_time time, departure_time time, check (arrival_time < departure_time)
);
insert into test values ('12:00', '14:30');
INSERT 20651 1
insert into test values ('12:00', '9:45');
ERROR: ExecAppend: rejected due to CHECK constraint $1
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Re: SQL CONSTRAINTS - Constraining time values from two attributes on
From
missive@frontiernet.net (Lee Harr)
Date:
> I was looking for a solution on how to write a constraint into a ' create > table ' expression that would ensure that one ' TIME ' attribute value > called arrival_time (declared as TIME) is allways constrained to have a > value that ensures it is allways after another attribute value called > departure_time (declared as TIME). > Any know how this constraint would be written?? > CREATE TABLE tablename (arrival_time timestamp,departure_time timestamp CHECK (arrival_time < departure_time) );