problems with computing a primary key column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problems with computing a primary key column

Author

Topic

gemini_shooter
Starting Member

12 Posts

Posted - 2006-09-13 : 15:26:35

hi !

Just started playing around with SQL 2000 and I createda sample table called 'actor' which has 4 columns

1. actorID
(formula= LEFT(NEWID(), 3)+ LEFT([actorFirst], 2) +
LEFT([actorLast], 2) + RIGHT(NEWID(), 3))
2. actorFirst
varchar(20) NOT NULL
3. actorLast
varchar(20) NOT NULL
4. actorName
(formula = [actorFirst] + ' ' + [actorLast]

Now my problem is that I want to set a primary key constraint on actorID but it doesn't let me because it the NULL check mark is automatically checked and I cannot check it off ... and I can't set a primary key on something which is allowed to be NULL....

I don't understand why 'actorName' column which is also calculated doesn't have that default NULL checked and locked ....

What am I doing wrong ? Please help ....

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-13 : 15:43:02

You would need to use the ISNULL function for each of the individual expressions in your actorid formula.

But do yourself a favor and don't use that kind of "intellegent key" as a primary key. Use either a natural key from the existing columns or create an identity column. Also, it really doesn't make sense to store actorName when you can derive it easily.

Be One with the Optimizer
TG

gemini_shooter
Starting Member

12 Posts

Posted - 2006-09-13 : 16:32:11

I am sorry I didn't really get the reply .... use the ISNULL function for each of the individual expression .... u mean to say modify this expression ... but where can I insert IS NULL

(formula= LEFT(NEWID(), 3)+ LEFT([actorFirst], 2) +
LEFT([actorLast], 2) + RIGHT(NEWID(), 3)) IS NOT NULL

I am little confused ..... also is this is a bad choice for a key ( is it because queries will have problems finding it)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-13 : 17:10:35

This is for MS Sql Server, right?

This is what I mean by using isNull for each expression:
ISNULL(left(newid(), 0),'') + ISNULL(left(actorFirst,2),'') + etc...

Yes, its a bad choice for a PK:
There are some philosophical reasons and some technical sql server reasons why builing "intellegence" into a value for a primary key is not a good idea. One reason is that when one of the attributes that make up the part of the code changes you will probably be tempted to change the primary key. (in this case if someone gets married and changes their last name) Updating a primary key to a new value is not a good idea for referential integrety reasons. There is lots of stuff written in Books Online and on this site concerning good and bad candidates for a primary key.

Be One with the Optimizer
TG

gemini_shooter
Starting Member

12 Posts

Posted - 2006-09-13 : 18:41:27

ISNULL(left(newid(), 3),'') + ISNULL(left(actorFirst,2),'') + ISNULL(left(actorLast,2),'') +
ISNULL(right(newID(), 3),'')

This didn't work .... it tells me the formula has an error .... The ISNULL replaces a NULL value with a supplied non null value....

DID u mean something like this
ISNULL(NULL, left(newid(), 3)) + ..etc

I tried this the formula stands correct but the it gives me the same error saying that it has trouble creating primary key on NULLABLE column ... I just don't understand why SQL thinks that this column is null ... all the other columns in the 'actor' table are specified to NOT NULL so none contain a NULL value and this value is going to be drevied from NON NULL values .... so how can it be NULL ... I wish I were more savy in T-SQL ... right now I am using the GUI for all this

another weird thing happened that .... I just setup a test table in a new database called actor and I assigned the 'actorID' a simple formula NEWID() and it automatically defaults to 'allow NULL' checkbox and I cannot disable it in the GUI ....

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-09-14 : 07:21:46

Your best bet it to make an IDENTITY column and let that be your primary key. THEN, go ahead and make your "Intelligent" column for whatever purpose. Just make sure you remember that the IDENTITY column is your PK. Sounds like you want to be able to show the primary key on a front end that a user will understand. Users don't need to see keys.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources