Why to use “not null primary key” in TSQL?

问题:

I am seeing too frequently "not null primary key" in scripts creating tables in TSQL in blogs, articles, books, forums, here in SO, etc.

For example, BING gives 630,000 results ((just in English) if to search against "not null" NEAR "primary key" "sql server":
http://www.bing.com/search?q=%22not+null%22+NEAR+%22primary+key%22+%22sql+server%22&go=&form=QBRE&filt=lf

I always perceived "NOT NULL" as inseparable part of definition of PRIMARY KEY, at least in SQL Server.
I tried to create a tables with Primary KEYs with and without "NOT NULL" in SQL Server but could not grasp the possible difference.

Why do all use "NOT NULL" for creating a primary key even in fast (short) illustrations?

---

Update:
How can NULL identify anything or be unique (as well as preventing multiple NULLs if one is permitted)? It is unspecified, missing, not applicable value

My question also implied subquestions:
if one defines "NOT NULL" for PK, why then UNIQUE is not specified?
And what is the definition of PK. IS it UNIQUE CONSTRAINT + NOT NULL or UNIQUE INDEX (then why NOT NULL)?
Plz give me link to msdn docs on it.

Update2: @Damien_The_Unbeliever

Why is not synonym without "NOT NULL"?

CREATE TABLE T3 
(
    PK int -- NOT NULL commented out
    , nonPK int -- to double-check my sanity 
    , constraint PK_vgv8 PRIMARY KEY (PK) on [PRIMARY]
) 

still does not permit NULL giving:

• ---

  Microsoft SQL Server Management Studio

  ---

  No row was updated.

  The data in row 2 was not committed. Error Source: .Net SqlClient Data Provider. Error Message: Cannot insert the value NULL into column 'PK', table 'TestData.dbo.T3'; column does not allow nulls. INSERT fails.

  The statement has been terminated.

  Correct the errors and retry or press ESC to cancel the change(s).

  ---

  OK Help

  ---

Update3:
This, concepts and terms definition, might appear as impractical nuisance.
It is not, some wrong statement (in the opinion of other(s) during communication/discussion) on a basic notion is enough to be considered a moron and create barriers in professional interaction and communication.

I forgot to tell, NOT NULL is scripted by SSMS for PK!

回答1:

Edited for clarity

According to the SQL Specification, a primary key can not contain NULL. This means that decorating a column with either "NOT NULL PRIMARY KEY" or just "PRIMARY KEY" should do the same thing. But you are relying on the SQL engine in question correctly following the SQL standard. As an example due to an early bug, SQL Lite does not correctly implement the standard and allows null values in non-integer primary keys (see sqlite.org/lang_createtable.html). That would mean for (atleast) SQLLite the two statements do two different things.

As "NOT NULL PRIMARY KEY" clearly indicates intent and continues to enfore non-null values even if the primary key is removed, that should be the prefered syntax.

回答2:

The following:

CREATE TABLE T1 (
    T1ID int not null primary key
)

is really shorthand for the following:

CREATE TABLE T1 (
    T1ID int not null,
    constraint <system generated name> PRIMARY KEY (T1ID) on [PRIMARY]
)

The column definition is really quite separate from the constraint definition. It's just that the shorthand hides the fact that it's two separate definitions. (Of course, if you use the long-hand form, you can do such things as defining a primary key across multiple columns).

Edit, in response to update. No, you still cannot have a nullable column in the primary key. But given that it's normal to specify the nullability of each column in a create table statement, it just feels odd to omit it (even though it's implied/required by the PRIMARY KEY constraint).

CREATE TABLE T1 (
    T1ID int,
    constraint <system generated name> PRIMARY KEY (T1ID) on [PRIMARY]
)

creates exactly the same table as the previous code sample. Explicitly specifying the "not null"-ability of a column is, then, more an explicit acknowledgement of the outcome, rather than a requirement.

And finally, you'll find it frequently occurring in example code because people have developed their database, and then used the built in SQL tools for generating a script from their database - the scripting tools always put everything explicitly.

回答3:

I don't know of any DBMS that permits a nullable column to be used in a PRIMARY KEY constraint. The SQL standard specifies that columns in a PRIMARY KEY constraint cannot be nullable.

In the relational model candidate keys consist of unique values, not nulls. It is a peculiarity of SQL that nullable columns can optionally be used in UNIQUE constraints. That feature is probably responsible for a lot of data quality problems. I highly recommend you avoid using nullable columns in UNIQUE constraints. If necessary, make a new table for the non-nullable values and put the UNIQUE constraint on that.

回答4:

It isn't required syntactically as this will be the default anyway if not specified for Primary Key columns regardless of the default setting for the other columns.

SET ANSI_NULL_DFLT_ON ON /*Set default for columns to allow NULL if not specified*/

CREATE TABLE #t
(
i INT PRIMARY KEY,
j INT
)

SELECT name,is_nullable
FROM tempdb.sys.columns 
WHERE object_id=object_id('tempdb..#t')


INSERT INTO #t VALUES (1,NULL) /*Succeeds as j allows NULL*/

INSERT INTO #t VALUES (NULL, 1)/*Won't succeed as column doesn't allow nulls*/

DROP TABLE #t

回答5:

The funny thing about MYSQL is the behavior is very good for when you pass a NULL (or 0) value to a primary key auto_increment field.

Say you have

create table Users(

  userId int primary key auto_increment

) ;

insert into users( userId ) values ( null ) ; -- insert OK
select * from Users ;  -- Shows 1

So if you pass 0 or NULL to an auto_increment field, it just selects the next available value.

If you take off the auto_increment property, an insert into a PRIMARY KEY field with NULL fails, because a PRIMARY KEY cannot be null, is the error. So the NOT NULL on PRIMARY KEY NOT NULL is implicit in MySQL at least.

回答6:

All primary key means it is the identifier for the table and therefor unique by definition. Unique doesn't mean not null, so if you have the requirement that the primary key can't be null you have to specify so explicitly. If you allow null you're just saying that it is ok to have a null value in the column, but you're only allowed to have one since it has to be unique.

回答7:

I seem to be going in another direction to explain why not null and primary key is also been accepted on a field when primary key itself implicitly holding not null.

Let us say we have table table1 which means it will hold its value to store 2 dimensional data. If i create a table with only one field it is acceptable, but what is the realtime usage of it in real object world? It will be one dimensional table just storing data of one field leading to No information, I believe. And keeping that field to accept Null values is meaningless.

Now, let us assume, we have defined a table with 2 fields which means we can pull good information from each row when filled with appropriate data.

Making those 2 fields accepting Null value isn't make any sense expect if we want to push garbage or expecting count(*) should result in an extra row.

So, my say is, putting not null is mandatory atleast for 2 fileds if we wish to use normalized tables with good fundamental RDBMS concepts. Before thinking whether the table needs primary key as a constraint, we always define teh filed with not null. And in additon to that if required use primary key and note that you may drop it any time but not 'not null'

And if we are sure that we wont drop primary key, use only primary key, otherwise both not null and primary key.

It all depends on usage and as i said, basic table defintions and Codd's rule must be attained to achieve good DB.