Both Key & unique key have unique values. But the basic difference is key
cannot have null values but unique key can accept null values as one null
is not equal to another null. Hope you are clear now.

"Null" is defined as "no value". It's not like a zero-length string,
which DOES have value (a string zero characters long), and we can talk
precisely about what its characteristics. But in SQL terms, null is an
absence of value. That means that every comparison to null returns
false, because otherwise some knowledge about its value is implied.

Let's say I have an integer column "x", and in one row it contains a
null. If you were doing SQL logical tests, here's what you'd come up with:

Is x equal to 5? No.
Is x greater than 5? No.
Is x less than 5? No.

What we just said is mathematically impossible ... but it's not SQL-y
impossible.

Nulls make for terrible default values because they mess up all your
comparisons. Nulls are crafted in the fiery depths of hell. Avoid them
whenever possible. Make sure you've got tolerable default values on all
your columns if it can be helped: '' for strings almost always works,
for example.

"Nulls make for terrible default values because they mess up all your
comparisons."

Seriously. So, in the case of your '' for a default value on a string. How do you determine if a user entered '' or if the value was skipped or missed. Nulls have a purpose and they create no problem if you know how to work with a database.

Yes, seriously. If you are saving the results from a data entry form
where the user skipped over a field, guess what value the form will
return for that field? Whatever the default string value for that field
is, which in may cases will be a zero-length string.

And if you're using a null value in a string to infer whether the user
visited that form at all, well, that's just needlessly breakable
programming. Use an explicit flag.

> Toolbox sql-l
> Reply from brett on Feb 26 at 9:26 AM
> "Nulls make for terrible default values because they mess up all your
> comparisons."
>
> Seriously. So, in the case of your '' for a default value on a string.
> How do you determine if a user entered '' or if the value was skipped
> or missed. Nulls have a purpose and they create no problem if you know
> how to work with a database.
>
>
>
>
>

Nulls have a very limited, specific and useful role to play. When a numeric column will be used in AVERAGE and similar functions and the numeric value is unknown or not apply, then the column should allow null, and probably default to null. Such numeric columns are updated frequently and hence are not good candidates to be in an index. In rare situations, it is pragmatic to denormalize a table and include columns where "Not Apply" is indicated by null due to the denormalization. There may be other situations. But they are rare. Nulls in an index, or a presumed key, often reflect a denormalization that is not pragmatic.

At least in DB2, nulls should be avoided in index columns as the inevitable SQL "OR" syntax makes the predicate unattractive for the Optimizer to choose to use the index. This is especially true of range predicates on dates: WHERE END_DT > CURRENT DATE OR END_DT IS NULL.

there is no null so there can't be two nulls.? null is an adjective not a noun.? trying to call a method on an uninstantiated object (null) yields an error -- no answer instead of ?a default answer.

there is no null stored, it is not a null value.? there is no value, it is null.

Requesting the records of all the members whose age we don't know tells us nothing about those ages.? We cannot make any distinction or compare our lack of knowledge.? There is no difference between our lack of knowledge of one members age against another and we can make no assertions about those values -- which we don't know.

More exactly (if that is possible), NULL is the answer that can mean more than one thing. In Oracle compliant SQL, a NULL answer is "no value", and it is "unknown answer", and "value absent".

When you code a query comparing a value to NULL, the Oracle compliant answer is "NULL". This is true EVEN WHEN NULL IS COMPARED WITH NULL, because "unknown answer" is infinitely different from "no value". So, NULL is not anything but NULL. NULL is not equal to NULL, nor greater, nor lesser.

When our data (and database) doesn't reflect the world around us, we have a good indication that the data (and database) is flawed.

In the real world, there are many meaningful circumstances in which our knowledge about something is NULL. The name of an unmarried person's spouse is NULL. The height of someone I know nothing about is NULL.

In fact, there can be two people whom I know nothing about -- are they the same height? Or is one taller than the other? The answer is NULL -- don't know, doesn't apply.

Our data (and database) ought to be able to reflect these real-world circumstances in an equally meaningful way. The real-world circumstance is that there are things that you don't know. If you don't know something, how do you describe it?

It is always problematic to store an actual value in the database when trying to represent an unknown, unknowable or irrelevant value.

We do it all the time, of course, because -- well, I don't know why. We use 9999-99-99 as the death_date of someone who is still alive. We use '' for an unmarried person's spouse's name. We use 0 for the employee_id of the CEO's manager. We use an empty string when we don't know a person's middle initial, then we use a string 'NMI' for a person who has "no middle initial".

There is an irrational fear of the concept of NULL -- maybe because it's harder to say "IS NULL" than to say "= 0". I don't know. But I do know that I have seen many databases in many companies and it's always been a nightmare to understand what values they use to represent "there is no value".

I use NULLs. I have never had a problem with NULL in SQL, and in fact found it to be a relief to have a standard representation of "I don't know" regardless of datatype or size. And the notion that NULL is not equal to NULL is reasonable when you think about the comparative height of two things you've never seen. But I began with the recognition that some things are unknown, some are unknowable, some are irrelevant.

I suppose this is one of the breakthroughs of being a DBA when you realise that NULL means unknown not blank

( another being using set theory and stop using cursors)

This is from Wikipedia
NULL
This special mark can appear instead of a value wherever a value can appear in SQL, in particular in place of a column value in some row. The deviation from the relational model arises from the fact that the implementation of this ad hoc concept in SQL involves the use of three-valued logic, under which the comparison of NULL with itself does not yield true but instead yields the third truth value, unknown; similarly the comparison NULL with something other than itself does not yield false but instead yields unknown. It is because of this behaviour in comparisons that NULL is described as a mark rather than a value. The relational model depends on the law of excluded middle under which anything that is not true is false and anything that is not false is true; it also requires every tuple in a relation body to have a value for every attribute of that relation. This particular deviation is disputed by some if only because E.F. Codd himself eventually advocated the use of specia
l marks and a 4-valued logic, but this was based on his observation that there are two distinct reasons why one might want to use a special mark in place of a value, which led opponents of the use of such logics to discover more distinct reasons and at least as many as 19 have been noted, which would require a 21-valued logic.[citation needed] SQL itself uses NULL for several purposes other than to represent "value unknown". For example, the sum of the empty set is NULL, meaning zero, the average of the empty set is NULL, meaning undefined, and NULL appearing in the result of a LEFT JOIN can mean "no value because there is no matching row in the right-hand operand".

"There is an irrational fear of the concept of NULL -- maybe because
it's harder to say "IS NULL" than to say "= 0". I don't know."

You start despising nulls when you have to program around nulls. Let's
take your example of the spouse's name for someone who's not married.
Every place you might have cause to display info about the person and
his spouse, you have to program around that possible null in the
spouse's name, or your program will crash when it tries to process the
null the same way it would a string. And if ANY field could potentially
contain a null, that's a lot of programming you have to do just to cope
with nulls.

The most efficient place to contend with nulls is the database itself,
so all the code downstream of the database can enjoy a mostly null-free
existence.

I agree that there's no good default value on dates, so I have to test
for null whenever dealing with dates. A zero-length string for a name,
on the other hand, is not a particularly ambiguous thing, so it makes
for a fine default.

> Toolbox sql-l
> Reply from Bryan Watson on Feb 27 at 10:45 PM
> When our data (and database) doesn't reflect the world around us, we
> have a good indication that the data (and database) is flawed.
>
> In the real world, there are many meaningful circumstances in which
> our knowledge about something is NULL. The name of an unmarried
> person's spouse is NULL. The height of someone I know nothing about is
> NULL.
>
> In fact, there can be two people whom I know nothing about -- are they
> the same height? Or is one taller than the other? The answer is NULL
> -- don't know, doesn't apply.
>
> Our data (and database) ought to be able to reflect these real-world
> circumstances in an equally meaningful way. The real-world
> circumstance is that there are things that you don't know. If you
> don't know something, how do you describe it?
>
> It is always problematic to store an actual value in the database when
> trying to represent an unknown, unknowable or irrelevant value.
>
> We do it all the time, of course, because -- well, I don't know why.
> We use 9999-99-99 as the death_date of someone who is still alive. We
> use '' for an unmarried person's spouse's name. We use 0 for the
> employee_id of the CEO's manager. We use an empty string when we don't
> know a person's middle initial, then we use a string 'NMI' for a
> person who has "no middle initial".
>
> There is an irrational fear of the concept of NULL -- maybe because
> it's harder to say "IS NULL" than to say "= 0". I don't know. But I do
> know that I have seen many databases in many companies and it's always
> been a nightmare to understand what values they use to represent
> "there is no value".
>
> I use NULLs. I have never had a problem with NULL in SQL, and in fact
> found it to be a relief to have a standard representation of "I don't
> know" regardless of datatype or size. And the notion that NULL is not
> equal to NULL is reasonable when you think about the comparative
> height of two things you've never seen. But I began with the
> recognition that some things are unknown, some are unknowable, some
> are irrelevant.
>
>
>
>
>

"The most efficient place to contend with nulls is the database itself, so all the code downstream of the database can enjoy a mostly null-free existence."
To this end, DBMS vendors have produced "IFNULL( )" functions that substitute some agreed-upon value in place of the NULL non-value. I can imagine an "IFNULL(spouses_name,'unmarried')" clause appearing in a VIEW definition along with "IFNULL(date_of_death,'not dead yet')" and other NULL-substitutes.
So here's my question: who is responsible for applying semantic meaning to data? The database is holding data, but the interpretation of that data is the responsibility of -- whom? Could be the database, could be the DBA, could be the programmer who stored it or the programmer who retrieved it, could be the user.
The data is "I don't know". If the meaning of "I don't know" is the same in every context (for that piece of data), then a universal substitution is appropriate. If it changes from one context to another, then those differences make a universal substitution kind of a problem.

A universal substitution, whether NULL or empty string or -1 or 9999-99-99 or a "Z" in the left-most position of an ID number -- whatever tool you use to designate "I don't know" -- doesn't solve the problems of "the code downstream". They still have to watch out for and work around these valueless values.

A side note on the Wikipedia cited earlier: "the sum of the empty set is NULL, meaning zero". That seems like the wrong answer -- the count of the empty set is zero, but the sum of the empty set is "there is no sum".

"A universal substitution, whether NULL or empty string or -1 or
9999-99-99 or a "Z" in the left-most position of an ID number --
whatever tool you use to designate "I don't know" -- doesn't solve the
problems of "the code downstream". They still have to watch out for and
work around these valueless values."

Yes and no on that. The problem with nulls, at the coding end, is that
they defy normal processing. A zero-length string for a name can be
displayed directly; a null will cause a run-time error. True, the
programmer needs to understand the meaning of the data, but if you can
relieve him of having to introduce code over and over because you're
sending him data likely to cause run-time errors, so much the better.
Again, I don't recommend it for something like dates, where there
probably isn't a good default date. But for strings, most of the time,
a zero-length string is clear enough; there is no circumstance where you
have a spouse whose actual name is a zero-length string.

The principle I'm operating under is, set up the database to facilitate
the smooth functioning of the software that accesses it, not to champion
the null as a valuable data concept.

> Toolbox sql-l
> Reply from Bryan Watson on Feb 28 at 1:23 PM
> "The most efficient place to contend with nulls is the database
> itself, so all the code downstream of the database can enjoy a mostly
> null-free existence."
> To this end, DBMS vendors have produced "IFNULL( )" functions that
> substitute some agreed-upon value in place of the NULL non-value. I
> can imagine an "IFNULL(spouses_name,'unmarried')" clause appearing in
> a VIEW definition along with "IFNULL(date_of_death,'not dead yet')"
> and other NULL-substitutes.
> So here's my question: who is responsible for applying semantic
> meaning to data? The database is holding data, but the interpretation
> of that data is the responsibility of -- whom? Could be the database,
> could be the DBA, could be the programmer who stored it or the
> programmer who retrieved it, could be the user.
> The data is "I don't know". If the meaning of "I don't know" is the
> same in every context (for that piece of data), then a universal
> substitution is appropriate. If it changes from one context to
> another, then those differences make a universal substitution kind of
> a problem.
>
> A universal substitution, whether NULL or empty string or -1 or
> 9999-99-99 or a "Z" in the left-most position of an ID number --
> whatever tool you use to designate "I don't know" -- doesn't solve the
> problems of "the code downstream". They still have to watch out for
> and work around these valueless values.
>
> A side note on the Wikipedia cited earlier: "the sum of the empty set
> is NULL, meaning zero". That seems like the wrong answer -- the count
> of the empty set is zero, but the sum of the empty set is "there is no
> sum".
>
>
>
>
>

No, it's not DIFFICULT to deal with nulls; it's just needless work that
is completely obviated by storing the data in formats that are more
useful to the software without sacrificing data integrity.

It's also not difficult to store dates as text strings in UTC format
and, as needed, convert them to numeric offsets from a given arbitrary
date; but what is gained by that? If you've got a fetish for UTC format
you'll be thrilled, but it certainly won't make the software work any
better and it will almost certainly slow down development and complicate
maintenance.

Like I said, the principle I'm operating under is, set up the database
to facilitate the smooth functioning of the software that accesses it,
not to champion the null as a valuable data concept.

It's not a big deal. Standard processes call of programs to access the
database through views, so in my world, it's always part of the view
specification to have the developer's needs on return data (this blank,
that null, etc). There is no reason to make life harder than is needed down
stream or any need sacrifice "purity" on the back end.

In my 30+ years as programmer then DBA and database designer, data modeller I have had far more problems with databases where NULL values are avoided no matter what. There are quite valid reasons for using NULL where the value is not known. That being said I think that for many reference tables, it is a good idea to include entries for "not known" and "not available". This allows foreign keys to these tables to always contain a value and also allows for a more descriptive reason for not having a real value.

Not wanting to cut off debate here, but we've drifted far afield of the original inquiry (KEY vs. UNIQUE KEY). This is all good discussion on the use and abuse of NULL values, but it probably deserves a thread of its own.

And, yes, I did take this thread astray -- I should have started a separate topic. My apologies.

there's nothing efficient about catering to programmers? perhaps you've never seen one of them try to come up with data designs before or heard how necessary it is for them to pass in query strings instead of specifying requirements for stored procedures.

programmers have to test for isDatabaseNull on every inspection and stop bothering decent people with their concepts of efficiency.

The title eventually actually raises a question; but first a bit of background. Keys is one of those terms that can have differing meanings depending upon the environment and the particular rdbms.

Primary Key is an attribute which uniquely identifies a row in a table and by definition cannot be Null. It is automatically also an index for rapid retrieval.

Unique Key – I interpret this as a Unique Index which can be on any other column in the table. Its purpose is to enable rapid retrieval and to ensure there are no duplicate values in the column (or columns) comprising that Index.

Key – I interpret this as an Index on any column (or columns) to aid rapid retrieval. Clearly any column comprising this Index can be Null.

A word of warning, though: a Primary Key does not guarantee that more than one row is the same except for the primary key itself, eg. a row comprising, say, a primary key + 6 columns does not guarantee that there are two or more rows where the 6 columns are the same. That guarantee has to be provided by way of checking the semantics of the data; the syntax is however correct.

Similarly for a Unique Index: the column (or columns) comprising the index, in addition to the primary key, does not guarantee that there are no duplicates in the remaining columns.

I attribute the need for a Key (= Index) to aid rapid retrieval based on that column (or columns).

After reading Bryan's explicit explanation of NULL, why would anybody expect to find Null values being used in an index – except perhaps to more easily identify which rows have NULL or NOT NULL as the reason for the search?

Footnote: we tend to go along with Francis Brickhill in that, in many instances, 'not known', 'not applicable' can be useful values. However, for example, in a 5 column address + zip(post)code, Null values are really the only practical way of coping with an address which uses only 3 columns: then, testing for 'Null' ensures that a blank line is not printed. An everyday, natural occurrence.

Brian Gooch: Thanks for a concise clarification of these "key" concepts of Uniqueness and Keys in SQL and databases generally -- one which goes directly to the original question. As you point out, they each serve a different purpose -- uniqueness for data integrity, keys (meaning indexes) for performance enhancement.

I've been a DBA for 12 years. In that time I have heard or read KEY in Primary Key, Unique Key, Candidate Key, Surrogate Key, Compound Key, Natural Key, Domain Key and Foreign Key. I've never heard Key by itself meaning index. Maybe I'm not that well read on the subject.

WPoling
Put to one side for the moment the fact that computing, including databases, contains many homonyms - where one word can mean several different things, or synonyms where several words can mean the same thing, and just focus on the simplest of essentials. Primary Key: a column where each value uniquely identifies a row in a table. Unique Index (sometimes called a Unique key) a column or columns, other than the primary key, used for enhancing the data integrity and rapid retrieval in queries.

For one thing, a PK is not necessarily one column.? Second, this is a very mechanical definition of primary key that might not serve one well -- and a debate that has been going on for years.

The role of a primary key is to assure that the real-world entity being represented in the database is represented uniquely.? The relational model depends on each row being unique, not just having a unique identifier column.? So, if you've got me in there twice with two different system generated GUIDs on the records, your database is flawed.? I understand that this type of distinction is lost on programmer types, but if you want to have a clearer picture of what you're doing with a relational database, it's something you should meditate on.

And it is a serious challenge!? If everything was an automobile with a VIN, the data architect's world would be a lovely place.? It is the architect and designer's job to pursue unique records not unique identifiers.

Having accomplished this, we can turn loose programmers and have some confidence that they we've done all we can to limit how they screw things up.

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.