Mar. 13th, 2007

rhu: (Default)

Seen on the walk back from Milk St. today, a Boston Police Department billboard with the headline:

Everday's an Adventure

rhu: (Default)

Seen on the walk back from Milk St. today, a Boston Police Department billboard with the headline:

Everday's an Adventure

SQL gripe

Mar. 13th, 2007 02:19 pm
rhu: (Default)
SQL is a standard language for expressing database design. It has the following useful constructs:

* A datum may be assigned the value NULL, which means (roughly) "there is no meaningful value to put here." NULL is not considered equal to any other value, even another NULL.

* A column (such as "the mayors of all the cities") may be constrained to have unique values ("no person can be the mayor of more than one city at the same time.")

But if you mark a column as unique, it won't allow more than one row to have NULL as its value, even though testing two NULLs for equality returns false. So there's no way to express "If there is a value here, it must be unique; but any number of rows may have an unknown or empty value at the same time."

Surely I'm not the first person to run into this problem. But I can't find any good solutions --- neither in my SQL books nor via Google.

Edited to add: Thanks to [livejournal.com profile] abbasegal for suggesting setting up a unique nonclustered index on a schemabound view.

SQL gripe

Mar. 13th, 2007 02:19 pm
rhu: (Default)
SQL is a standard language for expressing database design. It has the following useful constructs:

* A datum may be assigned the value NULL, which means (roughly) "there is no meaningful value to put here." NULL is not considered equal to any other value, even another NULL.

* A column (such as "the mayors of all the cities") may be constrained to have unique values ("no person can be the mayor of more than one city at the same time.")

But if you mark a column as unique, it won't allow more than one row to have NULL as its value, even though testing two NULLs for equality returns false. So there's no way to express "If there is a value here, it must be unique; but any number of rows may have an unknown or empty value at the same time."

Surely I'm not the first person to run into this problem. But I can't find any good solutions --- neither in my SQL books nor via Google.

Edited to add: Thanks to [livejournal.com profile] abbasegal for suggesting setting up a unique nonclustered index on a schemabound view.

Profile

rhu: (Default)
Andrew M. Greene

January 2013

S M T W T F S
  12345
6789101112
13141516171819
20212223242526
2728293031  

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags