3 ms·

How do you implement unique constraints then?

moron4hire 13y ago
The main take-away from my original comment should be "never destroy data". You may write new data, but you may never delete or overwrite data. How you design the database after that is up to you. The particular example I used is of course not going to be good for enforcing, say, uniqueness of email addresses across users. It has tended to make me really consider the notion of uniqueness within a database. What does the unique constraint mean? I think that people think of uniqueness to mean "at this point in time", but if you enforce it in the database, it means "for all time." In relational algebra, the unique constraint is really about enforcing a candidate key, a field that could satisfactorily be used as a key, but we've so happened to have chosen a different field as the actual primary key. In that context, given that we would want someone to be able to change their email address, I don't think "unique" email addresses make sense. So many things change over time and so many seemingly "unique" fields turn out to not be unique in the real world that, for the most part, I've given up on the unique constraint. Full names are neither unique, nor are they final (people get married and change their name). Not even Social Security Numbers are necessarily unique, for a variety of reasons (http://www.computerworld.com/s/article/300161/Not_So_Unique http://www.computerworld.com/s/article/300161/Not_So_Unique).
- einhverfr 13y ago
  That's what partial unique indexes allow you to put in the database. take this example which would work in PostgreSQL: CREATE UNIQUE INDEX profile_username_valid_idx_u ON profile(username) WHERE invalidated_at IS NULL; The problem with enforcing this in app-space is it doesn't get around the isolation problem. In both cases, you are assuming that the same profile won't get concurrently updated twice by two different transactions. Since only the database can referee this issue, you must enforce the uniqueness there. I.e. this check must happen on transaction commit and your application has no way of knowing if it did.

The main take-away from my original comment should be "never destroy data". You may write new data, but you may never delete or overwrite data. How you design the database after that is up to you. The particular example I used is of course not going to be good for enforcing, say, uniqueness of email addresses across users. It has tended to make me really consider the notion of uniqueness within a database. What does the unique constraint mean? I think that people think of uniqueness to mean "at this point in time", but if you enforce it in the database, it means "for all time." In relational algebra, the unique constraint is really about enforcing a candidate key, a field that could satisfactorily be used as a key, but we've so happened to have chosen a different field as the actual primary key. In that context, given that we would want someone to be able to change their email address, I don't think "unique" email addresses make sense. So many things change over time and so many seemingly "unique" fields turn out to not be unique in the real world that, for the most part, I've given up on the unique constraint. Full names are neither unique, nor are they final (people get married and change their name). Not even Social Security Numbers are necessarily unique, for a variety of reasons (http://www.computerworld.com/s/article/300161/Not_So_Unique http://www.computerworld.com/s/article/300161/Not_So_Unique).

einhverfr 13y ago
That's what partial unique indexes allow you to put in the database. take this example which would work in PostgreSQL: CREATE UNIQUE INDEX profile_username_valid_idx_u ON profile(username) WHERE invalidated_at IS NULL; The problem with enforcing this in app-space is it doesn't get around the isolation problem. In both cases, you are assuming that the same profile won't get concurrently updated twice by two different transactions. Since only the database can referee this issue, you must enforce the uniqueness there. I.e. this check must happen on transaction commit and your application has no way of knowing if it did.