SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 problems with computing a primary key column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gemini_shooter
Starting Member

12 Posts

Posted - 09/13/2006 :  15:26:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/13/2006 :  15:43:02  Show Profile  Reply with Quote
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
Go to Top of Page

gemini_shooter
Starting Member

12 Posts

Posted - 09/13/2006 :  16:32:11  Show Profile  Reply with Quote
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)
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/13/2006 :  17:10:35  Show Profile  Reply with Quote
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
Go to Top of Page

gemini_shooter
Starting Member

12 Posts

Posted - 09/13/2006 :  18:41:27  Show Profile  Reply with Quote
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 ....

Edited by - gemini_shooter on 09/13/2006 19:23:08
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 09/14/2006 :  07:21:46  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000