On Tue, Dec 8, 2009 at 4:00 AM, Thomas Polliard <[email protected]> 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:[email protected]
