Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
402 views
in Technique[技术] by (71.8m points)

sql server - 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!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...