openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daryl Stultz <da...@6degrees.com>
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 <thomas@polliard.com> 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:

CREATE TABLE users (
    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;
                                                                  count
-------
    62
(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.
http://www.6degrees.com
mailto:daryl@6degrees.com

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