Re: Deleted Flag/Unique Constraint
От | Lew |
---|---|
Тема | Re: Deleted Flag/Unique Constraint |
Дата | |
Msg-id | iYidndSvuqzo85HbnZ2dnUVZ_t-mnZ2d@comcast.com обсуждение исходный текст |
Ответ на | Re: Deleted Flag/Unique Constraint ("Bryan Murphy" <bryan.murphy@gmail.com>) |
Список | pgsql-general |
*Jonathan Hedstrom* wrote > Yes, something like this: > > CREATE UNIQUE INDEX index_name ON table_name ( unique_field) WHERE deleted=0; My only concern here is > Note: The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes toenforce unique constraints could be considered an implementation detail that should not be accessed directly. <http://www.postgresql.org/docs/8.1/interactive/indexes-unique.html> Why scare us off?. The semantics of unique indexes vary from DBMS to DBMS. For example, for IBM DB2: > When UNIQUE is used, null values are treated as any other values. For example, if the key is a single column that may containnull values, that column may contain no more than one null value. <ftp://ftp.software.ibm.com/ps/products/db2/info/vr9/pdf/letter/en_US/db2s2e90.pdf> But in PostgreSQL: > When an index is declared unique, multiple table rows with equal indexed values will not be allowed. Null values are notconsidered equal. <http://www.postgresql.org/docs/8.1/interactive/indexes-unique.html> MySQL evinces both behaviors, depending on which storage engine a table uses. > An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not applyto NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values forcolumns that can contain NULL. <http://dev.mysql.com/doc/refman/5.0/en/create-index.html> So I guess if portability of the full semantics is an issue, be careful. -- Lew
В списке pgsql-general по дате отправления: