Why specify primary key not null when creating a table?

Question

A piece of sql in sql server

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Primary key is not null by nature, why we still specify not null here? Do I miss anything?

check this: http://stackoverflow.com/questions/3905703/why-to-use-not-null-primary-key-in-tsql — Tiago Peczenyj, May 16 '12 at 00:11

score 4 · Answer 1 · answered May 16 '12 at 00:10

4

Yes, "primary key" is "not null", by definition.

Q: Do you object to typing in eight extra characters?

Q: Have you tried omitting "not null"? What happens?

answered May 16 '12 at 00:10

paulsm4

114,292
17
138
190

score 2 · Answer 2 · answered May 16 '12 at 00:10

2

You don't have to use it, it's redundant...

answered May 16 '12 at 00:10

gdoron

147,333
58
291
367

score 1 · Answer 3 · answered May 16 '12 at 01:22

1

Because Primary key will not contain NULL value. This key is the Identifier of the records.

answered May 16 '12 at 01:22

John Woo

258,903
69
498
492

score 0 · Answer 4 · answered May 16 '12 at 01:03

0

Because it is copy-paste from auto-generated script, and Not Null is coming from column settings, and primary key from constraint definition?

answered May 16 '12 at 01:03

Val Bakhtin

1,434
9
11

Why specify primary key not null when creating a table?

4 Answers4