openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daryl Stultz <>
Subject Re: Violation of unique constraint $$: duplicate value(s) for column
Date Tue, 08 Dec 2009 13:24:37 GMT
On Tue, Dec 8, 2009 at 4:00 AM, Thomas Polliard <> wrote:

> Perhaps I dont understand your goals but.....
> If you intend for the field to ALWAYS have a value for the row to be valid
> then NOT NULL is required.  If you intend for the rows to be unique but dont
> require the field for the row to be valid, it might make more sense to use a
> separate table.  Having a column on a table that is UNIQUE but NULLABLE is a
> bad design and can lead to confusion.
I have a users table with a timecardid column. A user may or may not have a
timecardid (it's just a text value, not a foreign key). If they have it, it
must be unique (nulls excluded). DDL looks like this:

    userid integer NOT NULL,
    timecardid character varying(50),

CREATE UNIQUE INDEX users_timecardid_unq ON users USING btree (timecardid);

It accepts nulls and rejects (non-null) duplicates:

db=# select count(*) from users where timecardid is null;
(1 row)

There are often a variety of ways of physically modelling the logical
design. Adding a one-to-one table adds an unnecessary complication. What's
confusing about "it's optional but needs to be unique if provided"?

Daryl Stultz
6 Degrees Software and Consulting, Inc.

  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message