Re: [NOVICE] Conditional constraint?

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