April 14, 2012 at 12:43 am
How can I set PK columns to allow null or blank?
April 14, 2012 at 1:32 am
If a column is defined as a primary key, then by definition it cannot be null, so you cannot do anything to allow nulls in there.
It can however (if the data type allows it, ie char, varchar etc) store one row with a blank value.
I guess there is more behind this quewtion though, what are you trying to do?
Mike
April 14, 2012 at 3:52 am
This link might help you:
April 14, 2012 at 6:08 pm
sukato7 (4/14/2012)
How can I set PK columns to allow null or blank?
You cannot do it.
A Primary Key is a Referential Integrity constraint that exclusively allows unique values and does not allow Null values.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 15, 2012 at 10:09 pm
PaulB-TheOneAndOnly (4/14/2012)
sukato7 (4/14/2012)
How can I set PK columns to allow null or blank?You cannot do it.
A Primary Key is a Referential Integrity constraint that exclusively allows unique values and does not allow Null values.
+1 Paul
April 16, 2012 at 1:43 pm
Mike John (4/14/2012)
If a column is defined as a primary key, then by definition it cannot be null, so you cannot do anything to allow nulls in there.It can however (if the data type allows it, ie char, varchar etc) store one row with a blank value.
I guess there is more behind this quewtion though, what are you trying to do?
Mike
I am confused with the statement above, "It can however (if the data type allows it, ie char, varchar etc) store one row with a blank value."
I thought primary key cannot have null value period? Please confirm
Regards,
TA
Regards,
SQLisAwe5oMe.
April 16, 2012 at 1:46 pm
SQLCrazyCertified (4/16/2012)
Mike John (4/14/2012)
If a column is defined as a primary key, then by definition it cannot be null, so you cannot do anything to allow nulls in there.It can however (if the data type allows it, ie char, varchar etc) store one row with a blank value.
I guess there is more behind this quewtion though, what are you trying to do?
Mike
I am confused with the statement above, "It can however (if the data type allows it, ie char, varchar etc) store one row with a blank value."
I thought primary key cannot have null value period? Please confirm
Regards,
TA
NULL <> ' ' -- blank (one space).
NULL <> '' -- empty string.
April 16, 2012 at 1:52 pm
Lynn Pettis (4/16/2012)
SQLCrazyCertified (4/16/2012)
Mike John (4/14/2012)
If a column is defined as a primary key, then by definition it cannot be null, so you cannot do anything to allow nulls in there.It can however (if the data type allows it, ie char, varchar etc) store one row with a blank value.
I guess there is more behind this quewtion though, what are you trying to do?
Mike
I am confused with the statement above, "It can however (if the data type allows it, ie char, varchar etc) store one row with a blank value."
I thought primary key cannot have null value period? Please confirm
Regards,
TA
NULL <> ' ' -- blank (one space).
NULL <> '' -- empty string.
That's if it is identified as unique key column right?......I know unique allows one null value, that's main difference between primary key and unique key?.....or am I confused?
Regards,
TA
Regards,
SQLisAwe5oMe.
April 16, 2012 at 2:04 pm
SQLCrazyCertified (4/16/2012)
Lynn Pettis (4/16/2012)
SQLCrazyCertified (4/16/2012)
Mike John (4/14/2012)
If a column is defined as a primary key, then by definition it cannot be null, so you cannot do anything to allow nulls in there.It can however (if the data type allows it, ie char, varchar etc) store one row with a blank value.
I guess there is more behind this quewtion though, what are you trying to do?
Mike
I am confused with the statement above, "It can however (if the data type allows it, ie char, varchar etc) store one row with a blank value."
I thought primary key cannot have null value period? Please confirm
Regards,
TA
NULL <> ' ' -- blank (one space).
NULL <> '' -- empty string.
That's if it is identified as unique key column right?......I know unique allows one null value, that's main difference between primary key and unique key?.....or am I confused?
Regards,
TA
You can create a primary key that will accept EITHER an empty string or a blank ' '. I get a key violation in my sandbox database table if i try to insert both. Only the first makes it in. I wonder if has to do with the setting of ANSI PADDING?
April 16, 2012 at 2:06 pm
SQLCrazyCertified (4/16/2012)
Lynn Pettis (4/16/2012)
SQLCrazyCertified (4/16/2012)
Mike John (4/14/2012)
If a column is defined as a primary key, then by definition it cannot be null, so you cannot do anything to allow nulls in there.It can however (if the data type allows it, ie char, varchar etc) store one row with a blank value.
I guess there is more behind this quewtion though, what are you trying to do?
Mike
I am confused with the statement above, "It can however (if the data type allows it, ie char, varchar etc) store one row with a blank value."
I thought primary key cannot have null value period? Please confirm
Regards,
TA
NULL <> ' ' -- blank (one space).
NULL <> '' -- empty string.
That's if it is identified as unique key column right?......I know unique allows one null value, that's main difference between primary key and unique key?.....or am I confused?
Regards,
TA
null is a construct to represent the lack of a value, so "null value" is a non-starter.
As far as the primary key is concerned, one of the requirements of a PK is to be addressable, so it has to be a value, called an address, which uniquely identifies the record it's associated to. Since null is not a value, a unique key containing a null cannot be addressable.
Quod Erat Dictum.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 16, 2012 at 2:24 pm
Matt Miller (#4) (4/16/2012)
SQLCrazyCertified (4/16/2012)
Lynn Pettis (4/16/2012)
SQLCrazyCertified (4/16/2012)
Mike John (4/14/2012)
If a column is defined as a primary key, then by definition it cannot be null, so you cannot do anything to allow nulls in there.It can however (if the data type allows it, ie char, varchar etc) store one row with a blank value.
I guess there is more behind this quewtion though, what are you trying to do?
Mike
I am confused with the statement above, "It can however (if the data type allows it, ie char, varchar etc) store one row with a blank value."
I thought primary key cannot have null value period? Please confirm
Regards,
TA
NULL <> ' ' -- blank (one space).
NULL <> '' -- empty string.
That's if it is identified as unique key column right?......I know unique allows one null value, that's main difference between primary key and unique key?.....or am I confused?
Regards,
TA
null is a construct to represent the lack of a value, so "null value" is a non-starter.
As far as the primary key is concerned, one of the requirements of a PK is to be addressable, so it has to be a value, called an address, which uniquely identifies the record it's associated to. Since null is not a value, a unique key containing a null cannot be addressable.
Quod Erat Dictum.
But, a unique index will allow one null value. A primary key will not.
April 16, 2012 at 9:54 pm
April 17, 2012 at 6:04 am
You can also create a unique filtered index, filtering out NULLS.
This effectively ignores NULLS but makes sure that NON NULL values are unique.
See here
But of course it depends on your purpose
April 17, 2012 at 6:29 am
As this is a SQL Server 2008 forum, the filtered index is available. If you have any SQL Server 2005 systems, though, it is not available there.
April 17, 2012 at 11:24 am
Not to beat a dead horse... Maybe I'm not.
1. A Primary Key cannot contain a NULL value.
2. You can place a unique constraint on a column, which will allow 1 NULL in that column.
3. If you have a unique constraint on multiple columns, "you can store nulls within those columns as long as you do not create a duplicate across the combination of nulls and actual data values." (Microsoft SQL Server 2008-Implementation and Maintenance, Hotek, 2009)
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply