SQL schema design issue - remove flags

Question

in our database schema, we like to use delete flags. When a record is deleted, we then update this field, rather than running the delete command. The rest of our queries then check the delete flag when returning data.

Here is the problem:

The delete flag is a date with a default value of NULL. This is convenient because when a record is deleted, we can easily see the date it was deleted.

However, for the unique constraints to be applied correctly, we need to include the delete flag in the unique constraint. The problem is that on MS SQL it behaves in accordance with what we want (for this project), but in postgresql, if any field in a unique constraint with several columns is NULL, it allows this field. This behavior conforms to the SQL standard, but it violates our design.

Possible options:

• enter the default value for the remote field as some hard-coded date

• add a bit flag for deletion, then each table will have 2 deletions of related fields - date_deleted and is_deleted (for example)

• change date_deleted to is_deleted (bit field)

, 1 - , , IsNUll. .

2 - 2 "" .

3, "". , , , .

, - ? , " "?

. ( ). , ( )

Answer 1

3, "", . , , .

, ? , -, ? =)

, , "": _Deleted, _CreatedStamp, _UpdatedStamp, _UpdatedUserId, _CreatedUserId... , , . " " " ". " ", , , .

Answer 2

, , ?

• .
• .
• .
• .

Answer 3

? , , , / , , , .

, "" , , , , .

Answer 4

:

CREATE UNIQUE INDEX i_bla ON yourtable (colname) WHERE date_deleted IS NULL;

Answer 5

is_deleted last_modified.

last_modified , ( , ). is_deleted TRUE, last_modified , .

last_modified GETDATE(). NULL, .

Answer 6

, , , ?

http://www.postgresql.org/docs/current/interactive/indexes-unique.html

Alternatively, you can store non-NULL and check that the deleted date is at least sql date = 0 or "1/1/1753" instead of NULL for restored records.

Answer 7

Is it possible to exclude the deleted date field from your unique index? How does this field contribute to the uniqueness of each record, especially if the field is usually null?