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.ApplyAction PK(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. 7 3897
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(I P,'')+ISNULL(DN S,''),''),
-- 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)
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_addres s VARCHAR(64) PRIMARY KEY,
ip_address VARCHAR(15) NULL, dns_address VARCHAR(64) NULL, CHECK
(network_addres s IN (ip_address,dns _address) AND
COALESCE(ip_add ress,dns_addres s) IS NOT NULL) /* Key must be either IP
or DNS */)
GO
/* Views enforce nullable unique constraints */
CREATE VIEW devices_ip_addr ess
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_addr ess (ip_address)
GO
CREATE VIEW devices_dns_add ress
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_add ress (dns_address)
GO
--
David Portas
SQL Server MVP
--
Clifford Heath (no@spam.please .net) 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,'')+I SNULL(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****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
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(I P,'')+ISNULL(DN S,''),''),
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...
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.
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(I P,'')+ISNULL(DN S,''),''),
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)
Clifford Heath (no@spam.please .net) 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_expressio n [ , 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****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Geoff Cayzer |
last post by:
At http://www.blueclaw-db.com/tips_tricks.htm I came across a section
which is included below and was hoping for some comment on the
article.
--------------
Almost never use this auto-number field as the primary key of the
table.
If you are thinking of hiring an Access programmer or consultant ask
|
by: Philip |
last post by:
Hey all,
(Access 2000)
I've been having a horror story with this design problem. My Database is
Structured like This: AUTHORS, BOOKS, PAGES. Those are the Tables and each
Item in each table needs a unique ID# based on its context.
Primary Keys
AUTHORS = AuthorID - NO Duplicates
|
by: Sebastien |
last post by:
Hi,
I am building a products database, linking sales and production. Each
part has a unique sales Stock Code and Production Number.
The sales stock code is a combination of letters and numbers taken
from the part name (BrandA 300 4 Bladed : B3004B). The production
number is a 4 or 5 digits number with first figure(s) indicating
category (BrandA 300 4 Bladed: BrandA is category 1000. This Product
would be 1003 if it was the third...
|
by: Neil |
last post by:
I have come accross a situation where I would like a primary key that is
based upon 2 fields. Firstly is this possible, and secondly is it good
practice?
Thanks
Neil
|
by: Thomas A. Anderson |
last post by:
I am a bit confused in creating a composite primary key. I have three table
with two of the tables containing primary keys. I have two of the tables
(each with a primary key) having one to many relations with the table
containing no primary key. Do I just create two primary keys on the table
that does not contain any primary key for this to become a composite primary
key?
Thank you in advance!
Still a newbie,
| |
by: Dave |
last post by:
Hi,
Maybe I'm missing something with the DataKeyField attribute of a datagrid
but it seems that it's somewhat limiting since this only allows you to
specify one field as the key.
I have a table that has two keys and it seems you need this value to find
the corresponding record in the DataSet in order to update the data from the
Datagrid.
|
by: GGerard |
last post by:
Hello
I'm trying to find the best way to set indexes and primary keys on MSAccess
tables
What are the advantages and disadvantages of indexes and primary keys?
What fields should be indexed?
Should I index more than one field per table?
What fields should have the primary key?
|
by: LurfysMa |
last post by:
Most of the reference books recommend autonum primary keys, but the
Access help says that any unique keys will work.
What are the tradeoffs?
I have several tables that have unique fields. Can I use them as
primary keys or should I define an autonum primary key?
One table has information about the 50 states in the US. The table
looks like this:
|
by: Dwight |
last post by:
Hi all,
Iam a first time user of any database. I am using A2003. I want to design a
database for county marriage records. So far, I have the following tables:
COUNTY: County GROOM: SurnameGr BRIDE: SurnameBr
MARRIAGEDATA: LicenseIssuedDate
Book GivenNameGr
GivenNameBr MarriageDate
Page AgeGr
|
by: vj_dba |
last post by:
Hi Group,
I have a Primary key in my table. It's clear Primary key wont allow
duplicates, this primary key creates one index for retrival. Suppose if
my table is having a Unique index also. Then what is the exact
difference between the Primary key and the Unique index?
Also which on data retrival..internally it uses the Primary key index
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |