Primary key on combination of nullable fields, at least one not-null

[Clifford Heath]

I have a case where a table has two candidate primary keys,
but either (but not both) may be NULL. I don't want to store
a copy of the concatenated ISNULL'ed fields as an additional
column, though that would work if necessary. Instead, I tried
the following (this is a related simplified example, not my
real one):

CREATE FUNCTION ApplyActionPK(
@IP int = NULL,
@DNS varchar(64) = NULL
)
RETURNS varchar(74) -- NOT NULL
AS
BEGIN
declare @val varchar(74)
set @val = str(ISNULL(@IP, 0), 10)
set @val = @val + ISNULL(@DNS, '')
return @val
-- Also tried "return str(ISNULL(@IP, 0), 10)+ISNULL(@DNS, '')"
-- Also tried "return ISNULL(STR(@IP, 10), ISNULL(@DNS, ''))"
-- ... and other things...
END
GO

create table ApplyAction( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as dbo.ApplyActionPK(ComputerID, DNS), -- PK value
-- Also tried "Target as ISNULL(STR(@IP, 10), ISNULL(@DNS, ''))"
CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target)
)

SQL Server always complains that the primary key constraint cannot be
created over a nullable field - even though in no case will the 'Target'
field be NULL.

Please don't explain that I should store an IP address as a string.
Though that would suffice for this example, it doesn't solve my
actual problem (where there are four nullable fields, two of which
are FKs into other tables).

What's the reason for SQL Server deciding that the value is NULLable?
What's the usual way of handling such alternate PKs?

Clifford Heath.

[Hugo Kornelis]

On Tue, 26 Apr 2005 15:49:23 +1000, Clifford Heath wrote:

I have a case where a table has two candidate primary keys,
but either (but not both) may be NULL. I don't want to store
a copy of the concatenated ISNULL'ed fields as an additional
column, though that would work if necessary. Instead, I tried
the following (this is a related simplified example, not my
real one): (snip)

Hi Clifford,

I don't really understand the above - you say that you don't want to store
the concatenated ISNULL'ed columns, then you present a UDF (user-defined
function) that concatenates the ISNULL'ed columns and add a computed
column with the result of that UDF...
What's the reason for SQL Server deciding that the value is NULLable?
The computed column is based on a UDF. The arguments to the UDF can be
NULL. From that, SQL Server concluded that the result might be NULL as
well. SQL Server won't check the source of the UDF for this, so regardless
of what you change in the UDF, the problem will persevere.
What's the usual way of handling such alternate PKs?

One way around this would be to to change the table def as follows:

create table ApplyAction( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as ISNULL(ISNULL(IP,'')+ISNULL(DNS,''),''),
-- Also tried "Target as ISNULL(STR(@IP, 10), ISNULL(@DNS, ''))"
CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target),
)

Another way is to include a surrogate key as primary key, and to declare
the Act, Target combination as a UNIQUE constraint. Or even omit the
computed column, ann declare (Act, IP, DNS) as UNIQUE constraint. The way
SQL Server treats NULL values in a UNIQUE constraint is not as I would
like it to be, but it is exactly what is needed for this case.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[David Portas]

A primary key must be non-nullable, by definition. Create another table
for the entity identified by IP/DNS and then reference that table's key
in ApplyAction. Unfortunately, SQL Server doesn't support
ANSI-compliant UNIQUE and CHECK constraints so it is much harder than
it should be to guarantee integrity.

CREATE TABLE Devices (network_address VARCHAR(64) PRIMARY KEY,
ip_address VARCHAR(15) NULL, dns_address VARCHAR(64) NULL, CHECK
(network_address IN (ip_address,dns_address) AND
COALESCE(ip_address,dns_address) IS NOT NULL) /* Key must be either IP
or DNS */)

GO

/* Views enforce nullable unique constraints */

CREATE VIEW devices_ip_address
WITH SCHEMABINDING
AS
SELECT ip_address
FROM dbo.Devices
WHERE ip_address IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX idx_devices_ip_address
ON devices_ip_address (ip_address)
GO
CREATE VIEW devices_dns_address
WITH SCHEMABINDING
AS
SELECT dns_address
FROM dbo.Devices
WHERE dns_address IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX idx_devices_dns_address
ON devices_dns_address (dns_address)
GO

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Clifford Heath ([e-mail]) writes:

What's the reason for SQL Server deciding that the value is NULLable?
Probably not a very good one. This is accepted in SQL 2005:

create table ApplyAction( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as ISNULL(IP,'')+ISNULL(DNS,'') persisted,
CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target),
)

Your UDF did not fly, because it had problems with determism. Not the
PERSISTED keyword, this is new for SQL 2005.

Unfortunately, the above is useless, as is Hugo's suggestion. Because
of the data-type precedence rules in SQL Server, DNS will be converted
to integer. Here is a version, ugly as it is, that works in SQL 2000:

create table ApplyAction4( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as ISNULL(convert(varchar(11), IP),'')+ISNULL(DNS,''),
-- Also tried "Target as ISNULL(STR(@IP, 10), ISNULL(@DNS, ''))"
CONSTRAINT PK_ApplyAction4 PRIMARY KEY(Act, Target),
)
What's the usual way of handling such alternate PKs?

Normally, I would go with an artificial primary key, typically an
identity column, and then have a UNIQUE constraint on (Act, IP, DNS).

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Clifford Heath]

Hugo Kornelis wrote:

I don't really understand the above - you say that you don't want to store
the concatenated ISNULL'ed columns, then you present a UDF (user-defined
function) that concatenates the ISNULL'ed columns and add a computed
column with the result of that UDF...
Without having checked, I assumed that the UDF would be called whenever
a value was desired. I assume you're telling me that the value will be
computed at INSERT or UPDATE and stored, not computed when needed?
The computed column is based on a UDF. The arguments to the UDF can be
NULL. From that, SQL Server concluded that the result might be NULL as
well. SQL Server won't check the source of the UDF for this, so regardless
of what you change in the UDF, the problem will persevere.
However it *does* check the UDF for determinism. Plus, the return value
is defined to be VARCHAR, not VARCHAR NULL - which you can't declare :-(
so I'd expect SQL Server to enforce that a non-null value was returned.
Target as ISNULL(ISNULL(IP,'')+ISNULL(DNS,''),''),
It appears I was close. Erland's version is identical except for using
CONVERT instead of STR, and is preferable to yours.
Another way is to include a surrogate key as primary key
Didn't want to do that. I like to have PRIMARY declared on my natural
keys, and use unique constraints on the synthetic key, if any. Plus,
our code generator prefers things that way, though it works both ways.
:-)
The way
SQL Server treats NULL values in a UNIQUE constraint is not as I would
like it to be

Nor is it what's documented in BOL :-(. Been there, fallen over that...

[Clifford Heath]

Erland Sommarskog wrote:

Target as ISNULL(convert(varchar(11), IP),'')+ISNULL(DNS,''),

Bingo! Convert() rather than Str().

I don't suppose I'm the only one surprised that these aren't equivalent?

Thanks everyone,

Clifford.

[Hugo Kornelis]

On Wed, 27 Apr 2005 14:35:20 +1000, Clifford Heath wrote:

Hugo Kornelis wrote:

I don't really understand the above - you say that you don't want to store
the concatenated ISNULL'ed columns, then you present a UDF (user-defined
function) that concatenates the ISNULL'ed columns and add a computed
column with the result of that UDF...

Without having checked, I assumed that the UDF would be called whenever
a value was desired. I assume you're telling me that the value will be
computed at INSERT or UPDATE and stored, not computed when needed?

Hi Clifford,

Yes and no :-)

Normally, a computed column is not computed at INSERT and UPDATE time and
not stored in the database; instead, the expression is evaluated when data
is read from the table. But this changes when you include the computed
column in an index - as soon as you do that, the expression will be
evaluated on INSERT and UPDATE and the result will be stored.

As far as I know, this behaviour is not different when the computed column
is based on a UDF.

Target as ISNULL(ISNULL(IP,'')+ISNULL(DNS,''),''),

It appears I was close. Erland's version is identical except for using
CONVERT instead of STR, and is preferable to yours.

Yep, you was. And so was I :-) Somehow, somewhere along the line I left
out the STR (which was included in your original version). I'm glad Erland
noticed that!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Clifford Heath ([e-mail]) writes:

Erland Sommarskog wrote:

Target as ISNULL(convert(varchar(11), IP),'')+ISNULL(DNS,''),

Bingo! Convert() rather than Str().

I don't suppose I'm the only one surprised that these aren't equivalent?

I will have to admit that I have banged my head against that one as
well. But if you look at the syntax for str(), it's all clear:

STR ( float_expression [ , length [ , decimal ] ] )

Anything with float in it is imprecise and indeterministic, and a computed
column with a float expression in it - directly or indirectly - cannot be
indexed.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp