current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite

I would like to find a way to store multiple addresses for one subject in my database, with only one default address per subject.

To cut a long story short, lets say we have a table:

CREATE TABLE test
(
  id integer NOT NULL,
  active boolean NOT NULL,
  CONSTRAINT pk_id PRIMARY KEY (id)
)

For each id in the table, there must be at most 1 true active value.

How could I achieve this?

Thanks.

share|improve this question
    
Actually you don't have to do anything since id being the primary key makes it unique so there cannot be two rows with the same ID. –  ThiefMaster♦ Jun 13 '12 at 12:37
    
If you don't want id as primary then you will need to write a trigger: postgresql.org/docs/9.1/static/sql-createtrigger.html but thats probably not what you want. –  Adam Gent Jun 13 '12 at 12:51

2 Answers 2

up vote 5 down vote accepted

Partial index is what you want:

create unique index some_index on test (id) where active;

As @ThiefMaster states primary index should be removed.

share|improve this answer
    
Thanks. That is fine because my PK is the address PK in this table. This also works: CREATE UNIQUE INDEX some_index ON test USING btree (active, id) WHERE active; –  greatkalu Jun 13 '12 at 12:46
    
You should notice that the only thing you have really changed in your query is adding active to index. While active is always true for all fields that a subject for indexing you just added some bloat. –  kworr Jun 13 '12 at 12:49
    
PK is just a sum of UNIQUE and NOT NULL constraints. –  kworr Jun 13 '12 at 12:51
    
Understood. Thank you kworr. Much appreciated!!! –  greatkalu Jun 13 '12 at 12:57
up vote 0 down vote

In this case it's no needed as explained previously but you can set constraints to 2 (non-PK) columns with different types as follows:

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (integer_column, boolean_column);

Note: A partial index sets an index built over a subset of a table, which has a completely different purpose.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.