Skip site navigation (1) Skip section navigation (2)

Re: Deleted Flag/Unique Constraint

From: Jonathan Hedstrom <jhedstrom(at)desc(dot)org>
To: Bryan Murphy <bryan(dot)murphy(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Deleted Flag/Unique Constraint
Date: 2007-03-30 00:05:12
Message-ID: 460C5438.1000509@desc.org (view raw or flat)
Thread:
Lists: pgsql-general
Bryan Murphy wrote:

> Is it possible to declare a unique constraint in combination with a 
> deleted flag?
>
> For example, if I have a table like this:
>
> CREATE TABLE
> (
>   ID NOT NULL PRIMARY KEY,
>   Key VARCHAR(32) NOT NULL,
>   Value VARCHAR(32) NOT NULL,
>   Deleted INT NOT NULL DEFAULT 0
> );
>
> can I declare a unique constraint that only checks Key and Value when 
> Deleted = 0?

Yes, something like this:

CREATE UNIQUE INDEX index_name ON table_name ( unique_field) WHERE 
deleted=0;

-Jonathan

In response to

Responses

pgsql-general by date

Next:From: John D. BurgerDate: 2007-03-30 00:06:06
Subject: Re: Deleted Flag/Unique Constraint
Previous:From: Tom LaneDate: 2007-03-29 23:53:38
Subject: Re: Some encoding trouble via libpq

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group