Thread: Re: [NOVICE] Conditional constraint?

Sorry it has taken me so long to get back to you but I have been on
holiday!!!! :o)

OK, here's what I want to do.... The SQL for the table is like this

Table: tblprojectsummary
CREATE TABLE "tblprojectsummary" (
  "fldpjctno" int4 DEFAULT nextval('tblprojectsummary_fldpjctno_key'::text)
NOT NULL,
  "fldpjcttitle" varchar(25),
  "fldpjctsummary" text NOT NULL,
  "fldpjcttype" int4,
  "fldpjctclientid" int4,
  "fldpjctclientcontactid" int4,
  "fldpjctcompany" varchar(50) NOT NULL,
  "fldpjctdivision" int4,
  "fldpjctresearchexec" int4 NOT NULL,
  "fldpjctsecresearchexec" int4,
  "fldpjctsubclient" varchar(50),
  "fldpjctsector" varchar(50) NOT NULL,
  "fldpjctresearchtype" varchar(50) NOT NULL,
  "fldpjctpublic" bool,
  "fldpjcttopic" varchar(50) NOT NULL,
  "fldpjctmethod1" varchar(50) NOT NULL,
  "fldpjctmethod2" varchar(50),
  "fldpjctmethod3" varchar(50),
  "fldpjctstatus" varchar(50) NOT NULL,
  "fldpjctpropdate" date NOT NULL,
  "fldpjctpropduedate" date,
  "fldpjctcommdate" date,
  "fldpjctunsuccdate" date,
  "fldpjctunsuccwhy" text,
  "fldpjctfwcommdate" date,
  "fldpjctreportdate" date,
  "fldpjctcompdate" date,
  "fldpjctbudget" float8,
  "fldpjctarea" int4 NOT NULL,
  "fldpjctnotes" text,
  "fldpjctsubcont" bool,
  "fldpjctfolder" varchar(100),
  "fldpjctsuccess" varchar(10),
  "fldpjctweighting" float8,
  "flddateadded" date,
  "flduseradded" varchar(15),
  "flddatemodified" date,
  "fldusermodified" varchar(15);

when someone changes "fldpjctstatus" to a value (ongoing) I want to make
"fldpjcttitle" NOT NULL. How do I do this?

Thanks

Nick (Confused Novice!!!)

-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: 08 March 2002 18:37
To: Nick Haw; 'pgsql-general@postgresql.org';
'pgsql-novice@postgresql.org'; 'pgsql-novice@postgresql.org';
'pgsql-novice@postgresql.org'
Subject: Re: [NOVICE] Conditional constraint?


Nick,

> What I want to do is make a field so that it requires a value after a
>  change
> in another field to a particular value. Is this possible with the ADD
> CONSTRAINT command, or will I have to make a custom function and use
>  a
> trigger.

That depends on how complicated your rules are.  All constraints must
 evaluate to a true/false statement, so complex procedures require a
 function + trigger instead.  How about posting some more details?

-Josh

On Thu, 21 Mar 2002, Nick Haw wrote:

> Sorry it has taken me so long to get back to you but I have been on
> holiday!!!! :o)
>
> OK, here's what I want to do.... The SQL for the table is like this
>
> Table: tblprojectsummary
> CREATE TABLE "tblprojectsummary" (
>   "fldpjctno" int4 DEFAULT nextval('tblprojectsummary_fldpjctno_key'::text)
> NOT NULL,
>   "fldpjcttitle" varchar(25),
>   "fldpjctsummary" text NOT NULL,
>   "fldpjcttype" int4,
>   "fldpjctclientid" int4,
>   "fldpjctclientcontactid" int4,
>   "fldpjctcompany" varchar(50) NOT NULL,
>   "fldpjctdivision" int4,
>   "fldpjctresearchexec" int4 NOT NULL,
>   "fldpjctsecresearchexec" int4,
>   "fldpjctsubclient" varchar(50),
>   "fldpjctsector" varchar(50) NOT NULL,
>   "fldpjctresearchtype" varchar(50) NOT NULL,
>   "fldpjctpublic" bool,
>   "fldpjcttopic" varchar(50) NOT NULL,
>   "fldpjctmethod1" varchar(50) NOT NULL,
>   "fldpjctmethod2" varchar(50),
>   "fldpjctmethod3" varchar(50),
>   "fldpjctstatus" varchar(50) NOT NULL,
>   "fldpjctpropdate" date NOT NULL,
>   "fldpjctpropduedate" date,
>   "fldpjctcommdate" date,
>   "fldpjctunsuccdate" date,
>   "fldpjctunsuccwhy" text,
>   "fldpjctfwcommdate" date,
>   "fldpjctreportdate" date,
>   "fldpjctcompdate" date,
>   "fldpjctbudget" float8,
>   "fldpjctarea" int4 NOT NULL,
>   "fldpjctnotes" text,
>   "fldpjctsubcont" bool,
>   "fldpjctfolder" varchar(100),
>   "fldpjctsuccess" varchar(10),
>   "fldpjctweighting" float8,
>   "flddateadded" date,
>   "flduseradded" varchar(15),
>   "flddatemodified" date,
>   "fldusermodified" varchar(15);
>
> when someone changes "fldpjctstatus" to a value (ongoing) I want to make
> "fldpjcttitle" NOT NULL. How do I do this?

Maybe a check constraint:
CHECK fldpjctstatus != 'ongoing' or fldpjcttitle IS NOT NULL