What is the difference between Primary key and Not NULL constrain

Question

0.00/5 (No votes)

See more:

I want to know what is the basic difference between this two constrains. If it is possible then please explain with example.

Posted 4-Dec-14 21:02pm

Bittu14

Add a Solution

8 solutions

Solution 3

In a nutshell, both constraints don't allow NULL values however Primary key needs to be unique, NOT NULL column need not be.

Secondly, primary key, by default, creates a clustered index so as to improve query performance. Moreover, there can be only one primary key per table which is not the case for NOT NULL.

Posted 4-Dec-14 22:20pm

Harsh Athalye

Solution 2

if you google it you can find more resources related to this:

http://www.studytonight.com/dbms/sql-constraints.php[^]

Posted 4-Dec-14 22:18pm

King Fisher

Solution 4

We use the primary key to control what row is to be updated, a primary key is always also marked as not null and institutes the identification of that particular row of data it can be of one or several fields.

Marking a field to be not null, indicates that that field MUST have a value. If a field is marked to allow null, the DB typically stores the value "\0" (without the quotes) this is null and indicates to the RDBMS that there is no value for this field.

Really important detail to get for sure, about sql server(s)

Posted 5-Dec-14 2:00am

Thomas Nielsen - getCore

Updated 5-Dec-14 2:01am

v2

Comments

Lolo1986 5-Dec-14 19:20pm

Hi Thomas,

"We use the primary key to control what row is to be updated," this is not true at all ! You can update a table base on whatever field you want. The primary is here only to identity uniquely each row of the table.

Thomas Nielsen - getCore 8-Dec-14 3:08am

Lolo ... ahem, primary= first, key= can unlock. Have you even begun to see the execution plans for queries that dos not use indexes?
Trust me the purpose of the primary key is exactly as i stated, but don't take my word for it, what about w3 schools? http://www.w3schools.com/sql/sql_primarykey.asp (notice that primary key is ALSO a contraint which makes it superflous to mention that both things are called that too, for the understanding of the usage.)
heh, and you state "the primary is here only to identifiy uniquely" in what way is that different to "institutes the identification of that .. row" ? You're even saying the same thing :)
And again 'we' meaning me and the people i work with, DO use primary keys for updating, because it is by far the fastest method and a prerequisite for linq to sql and entity framework to function properly.

Lolo1986 8-Dec-14 12:28pm

Like you said, you can update a DB with the help of the PK, if you want to update a row.The execution plan is going to use the PK.BUT you can have a scenario where you need to update a data set, in this scenario you don't want to create a cursor to loop through each records but use an update statement based on some criteria. At this time you will realize that you execution plan won't use the PK but certainly a different index based on you WHERE clause, or maybe scan you PK, which you don't want. I am agree with most of the things you said, but it is really dangerous of saying the PK is use to update a row. You will notoce in your article in W3 Schools, they are not mentioning that a PK is used to update a row, neither on MSDN. The primary objective of the PK is uniquely identify each row of a table to enforce the integrity of the data. We are pretty much saying the same thing on 2 different point of views.

http://msdn.microsoft.com/en-us/library/ms179610.aspx

Thomas Nielsen - getCore 8-Dec-14 16:17pm

i would never update anything based on a cursor, in fact the only type of cursor i would ever use is a forward_only one and that would be designed to fish out the id's i would want. Mostly i use temp tables for that purpose though.
Perhaps saying that I use the key for updating is a bit unclear from some angle and since we agree it is the unique identity of a row i think we're close enough to eachother and the differences mostly due to the translation, and that your original 'it is not true at all' which spawned by need to explain how that wasn't entirely all wrong since it most certainly is what we select, update and join by most of the time. I dare say, most of us in the business of information systems with relational databases worldwide. But you are of cause right that the role of the primary key is to identify a row uniquely and the fact that this makes it usefull to control what to do with that row is with sufficiently nerdy glasses on, only secondary B-)

Solution 5

A primary Key is a constraint uniquely identity each row in a table. It does create a Cluster index by default. A primary key can have one or multiple column. You can have only 1 primary key per table. A primary cannot have NULL value.

NOT NULL is a constraint that will not avoid any NULL value in a column.

Here an example when you create a table.

SQL

CREATE TABLE myTable
( 
myTablePK INT PRIMARY KEY,
myTableNotNull INT NOT NULL
)

So let' try to insert a value in our table

example 1:

SQL

INSERT INTO myTable(NULL,2)

This will trow an error because the PK cannot be NULL.

example 2:

SQL

INSERT INTO myTable(1,2)

This will work cause myTablePK= 1 doesn't exist.

example 3:

SQL

INSERT INTO myTable(1,2)

This won't work because myTablePK= 1 already exist. PK must be unique.

example 4:

SQL

INSERT INTO myTable(2,NULL)

This won't work because myTableNotNull cannot be NULL.

example 5:

SQL

INSERT INTO myTable(2,2)

This will work !

Have a good week-end guys !

Posted 5-Dec-14 13:18pm

Lolo1986

Solution 6

PK is not null and not allow duplicate ,but not null allow duplicate values

Posted 5-Dec-14 17:31pm

Kan07

Solution 7

Primary Key must be unique. Often used with AUTOINCRMENT to form the ID of newly a INSERTed row. With this ID, it's easier to UPDATE the correct row. NOT NULL means that the column must have a value in it and you can specify a DEFAULT VALUE.

Same Question Posted Here..
http://stackoverflow.com/questions/20404730/what-is-difference-between-unique-key-with-not-null-constraint-and-primary-key[^]

Posted 5-Dec-14 21:04pm

/\jmot

Updated 5-Dec-14 21:05pm

v2

Solution 8

hi,
check this

Primary Key:

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Most tables should have a primary key, and each table can have only ONE primary key.

NOT NULL constraint:

The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

The following SQL enforces the "PId" column and the "Name" column to not accept NULL values:

CREATE TABLE Persons
(
PId int NOT NULL PRIMARY KEY,
Name varchar(255) NOT NULL,

)

PID Name
1 AAAA
2 AAAA
3 BBBB

Posted 6-Dec-14 0:04am

mudgilsks

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shweta N Mishra · Accepted Answer · 2014-12-04T21:15:00

Solution 1

Primary key allows only unique value across table and does not allows NULL to be stored.
Not Null constraint also wouldnt allow to store NULL but you can have values which are duplicates.

Posted 4-Dec-14 21:15pm

Shweta N Mishra

Comments

King Fisher 5-Dec-14 4:15am

5+ :)

Shweta N Mishra 5-Dec-14 4:15am

thank you.