I have a table that has 3 columns: firstname, middlename and lastname.
The concatanation of the 3 columns will always be unique.
I would like to create a fourth column called fullname that would have
[firstname]+ ' ' + [middlename] + ' ' + [lastname] as a formula and make this
the primary key.
However the middlename column can, and often does, have Null values. Becuase
the middlename column can hae null values I am having trouble using this as
part of my primary key field. If 2 people have the same first and last name
and middle name will always be included for one of the names to ensure unique
values.
Any suggestions are appreciated. Thanks

Hi,

Create a Unique key based on First, Middle and Last names. Unique key allows
one null for this combination.

THanks
Hari
SQL Server MVP

Show quoteHide quote

Show quote Hide quote Create a field called ID as Guid or int and make that the primary key. Even
though some professor somewhere will tell you this is incorrect most
programmers use this method.

Michael

James wrote:
You can have more than one attribute as a primary key, so make primary
key as firstname, middlename and lastname. You can evaluate fullname  as

[firstname] + ' ' + coalesce([middlename], ' ') + ' ' + [lastname]

Make either a view that contains the fullname, or add the fullname to
the original table and make a  trigger that evaluates new value each
time some of the firstname, middlename or lastname is added or changed.

This adds extra spaces when "middlename" is NULL, eg:

'John' + ' ' + COALESCE( NULL, ' ' ) + ' ' + 'Doe'

will give:

'John   Doe'

Try this:

[firstname] + ' ' + ISNULL( [middlename] + ' ', '' ) + [lastname]

It should give 'John Doe' (but still 'John Kevin Doe' if the
"middlename" is "Kevin").

James wrote:
James wrote:

Why? No-one has a null for a middle name. What does the uniqueness of
these three columns mean to you if the middle name is "missing"
(unknown?) for some reason?

ALTER TABLE tbl
ALTER COLUMN middlename VARCHAR(20) NOT NULL;

ALTER TABLE tbl ADD CONSTRAINT pk_tbl
PRIMARY KEY (firstname, middlename, lastname);


And what if two people have the same middle name as well as first and
last name?

David Portas wrote:
Why mother does, if you interpret NULL as "data does not exist".

That is the problem with NULLs -- subjective interpretation. You interpret
NULLs in one way, your neighbor does in another and a bystander does it
differently.

So when using NULLs, how can you formalize your facts for logical
representation and how does a formal DBMS system infer new facts from
existing facts?

The way in which the DBs author interprets it would be the correct
interpretation and all other's should follow. Naturally the author would
have documented this :-)

Michael

On Wed, 06 Sep 2006 08:31:41 +0300, Arto Viitanen wrote:

Hi Arto,

Since NULL is defined as a marker for missing data, you'd be using it
incorrect. Since you know that mother has no middle name, the data is
not missing, it's an empty string. You'd have to use NULL to store my
middle name, since (as far as I know) I never disclosed anywhere on the
internet whether or not I have one - so you can't know and hance can't
enter anything, not even an empty string.

Of course, you can choose to use NULL to represent the (known) absence
of a middle name. Just as you can choose to use 3 to represent the
number 75 and 75 to represent the number 3. And as long as all your
front-end code is built around this non-standard interpretation of 3 and
75, you won't run into trouble - but you will find that you can't use
most of SQL Server's builtin functions and expressions, since they don't
share your non-standard interpretation of 3 and 75.

Are you sure about this? I have 2 friends of mine which have exactly the same name, from
first to last.

Most DBA's will tell you that this is bad practice. Never let business data (broad term,
OK) be the PK for a table, for at least 2 reasons:
1) to uniquely identify a person from this table, you would need to include all fields (3
in your case) into referencing tables, and
2) business data has a tendency to change over time. What if someone mistyped a person's
name and later on this is corrected? If something changes in the PK, all your related
tables need to be updated too!

It is much easier to create a new field and make it an IDENTITY and Primary Key. This way
SQL Server ensures that each key will be unique. Next, if you are sure your rule that all
3 fields will be unique will hold all the times, create a unique index on those 3 fields.
Now, when referencing a person from your table, all you need to do is include this
single-field PK into the table. It's reference will still be valid even if the persons
name would change...

Hope this helps.
Success!

....Arjan...

Postcode problem
Query Problem
About Cursor on Dynamically created Tables.
Creating 2nd transaction form inside db ?
import 1 mln records = problem