1

which constraint makes sure a column has some value entered? I am confused between primary key and not null constraint .

Shouvik
  • 449
  • 1
  • 7
  • 17
  • Null is a special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database. See this for more details [Null (SQL)](http://en.wikipedia.org/wiki/Null_(SQL)) –  Mar 10 '13 at 21:04
  • The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain unique values. taken from [SQL PRIMARY KEY Constraint](http://www.w3schools.com/sql/sql_primarykey.asp) –  Mar 10 '13 at 21:06
  • The NOT NULL constraint enforces a column to NOT accept NULL values. taken from [SQL NOT NULL Constraint](http://www.w3schools.com/sql/sql_notnull.asp) –  Mar 10 '13 at 21:09

3 Answers3

3

A NOT NULL constraint.

All columns that participate in a PK must also not allow NULL but the PK constraint guarantees something more, uniqueness, - i.e. no two rows in the table can have the same value for the primary key.

In SQL Server even though syntactically you can name a NOT NULL constraint in the DDL it is different from other constraints in that no metadata (including even the name) is actually stored for the constraint itself. 

CREATE TABLE T
(
X INT CONSTRAINT NotNull NOT NULL
)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
1

Another point I didn't see addressed: NULL and empty string are two very different things, but they are often deemed interchangeable by a large portion of the community.

You can declare a varchar column as NOT NULL but you can still do this:

DECLARE @x TABLE(y VARCHAR(32) NOT NULL);

INSERT @x(y) VALUES('');

So if your goal is to make sure there is a valid value that is neither NULL nor a zero-length string, you can also add a check constraint, e.g.

DECLARE @x TABLE(y VARCHAR(32) NOT NULL CHECK (DATALENGTH(LTRIM(y)) > 0));
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
0
NOT NULL

is the condition that a field has a value. You can enforce that a field always have a value entered for every record inserted or updated, making the field NOT NULL in the table definition.

A primary key must meet these three conditions:

  1. The values of the field are NOT NULL.
  2. The values are unique.
  3. The values are immutable.

The database can enforce the first two conditions with a unique index (and a not null condition on the field).

The third condition is not typically enforced by the database. Databases will typically allow changes to primary key fields, so DBAs can "fix" them. So the third condition is more philosophical, in that you agree to use the key for identification, and not write an application which changes the value, unless intended for an administrator to fix the keys.

I have been using field here, but a primary key can be a compound primary key, made up of any combination of fields which meets the conditions. Any combination of fields which matches the first 2 or all 3 conditions is called a candidate key.

Only one candidate key can be used as the primary key. Which one is just an arbitrary choice.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Marlin Pierce
  • 9,931
  • 4
  • 30
  • 52
  • 2
    SQL Server does not enforce item 3 on your list. – Martin Smith Mar 10 '13 at 21:11
  • 1
    @MartinSmith Neither does DB2, Oracle or MySQL. I suspect it is common practice to leave condition 3 unenforced by the database. However, condition number 3 is part of the definition in relational database theory. – Marlin Pierce Mar 10 '13 at 21:17
  • 1
    I see your point but stability seems much more widely cited than immutability. [See for example answers here](http://stackoverflow.com/q/3632726/73226) – Martin Smith Mar 10 '13 at 21:22
  • And in so far as Wikipedia can be trusted ["However, neither of these restrictions is part of the relational model or any SQL standard."](http://en.wikipedia.org/wiki/Unique_key) – Martin Smith Mar 10 '13 at 21:27