| Author |
Topic |
|
kelleyb
Yak Posting Veteran
61 Posts |
 Posted - 2003-06-23 : 18:18:44
|
In general, I'm sure having a primary key that is established directly by the data in the INSERT is probably favorable. However, does anyone see a downside to changing the primary key using the computed value of two columns. For instance, I would very much like to set the primary key for a given table based on a bit of string manipulation from two other columns. Something like this.CREATE TABLE table1 ( col1 uniqueidentifier, col2 nchar(4), col3 nchar(8) NOT NULL CONSTRAINT PK_col3 PRIMARY KEY CLUSTERED)CREATE TRIGGER TIU_col3 ON table1 FOR INSERT, UPDATE ASIF UPDATE (col2) UPDATE table1 SET col3 = LEFT(col1, 4) + LEFT(col2, 4) WHERE col2 = (SELECT col2 FROM inserted) Any thoughts are appreciated.-Brian |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-06-23 : 18:38:01
|
| Brian,SQL 2K supports Primary Keys on computed columns....It has a few tricks to it...[url]http://www.microsoft.com/sql/techinfo/tips/development/July2.asp[/url]DavidM"SQL-3 is an abomination.." |
 |
|
|
kelleyb
Yak Posting Veteran
61 Posts |
Posted - 2003-06-23 : 18:46:43
|
<evil laughter>MUUUHHHAAAHHHAAAHHHAAAHHHAAA</evil laughter>  Thanks Mister!  -Brian |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-06-24 : 03:21:48
|
Brian:Firstly, the whole idea of having a Primary key is to a unique identifier for a row in the table. Looking at the trigger code you posted, I think that either col1 or col2 is already doing the job of uniquely identifying the row. Could'nt that be used as your primary key (and the computed column could stay, but not as primary key)? If not, then you will need to check for UPDATE of both the columns used to create the primary key (col1 and col2). A whole GUID is guaranteed to be unique, but not the first 4 characters  MOOSecondly, this trigger code will fail if more than one row is inserted or updated by a single statement. Needs only a small modification:UPDATE table1 SET col3 = LEFT(col1, 4) + LEFT(col2, 4) WHERE col2 IN (SELECT col2 FROM inserted)Owais |
|
|
|
kelleyb
Yak Posting Veteran
61 Posts |
Posted - 2003-06-24 : 11:02:28
|
quote:
"...the whole idea of having a Primary key is to a unique identifier for a row in the table..."
See, these are still some of the conceptual problems I'm facing. I honstly can't put my finger on the cause. Either it's due to my lack of in-depth experience with T-SQL or it's maybe because I've read a couple articles on normalization, and administering SS2k via scripts (not the Ent Admin), and now I'm just making things too difficult for a relatively simply project.I thought about using the uniqueidentifier, but I need to ensure that one of the other columns is unique. Using a GUID, theoretically, I could have the same value in another column that's important. I honestly like the idea David suggested above. I don't know if it would be considered "non-standard" db design, but it looks like it would work.What do you think?-Briandba-wannabe |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-24 : 12:12:22
|
quote:
Brian:Firstly, the whole idea of having a Primary key is to a unique identifier for a row in the table.
Actually, it's more than that...and if you're starting out I would SLOW down on the complex stuff and read as much as you can, google, search this site on the practices of good database design...starting with the logical, then worrying about physical implementation...Read this:http://www.serverwatch.com/tutorials/article.php/1549781Brett8-) |
|
|
|
kelleyb
Yak Posting Veteran
61 Posts |
Posted - 2003-06-24 : 12:27:48
|
quote:
"...and if you're starting out I would SLOW down on the complex stuff and read as much as you can..."
Hey Brett,Unfortunately, I'm in one of those situations where I'm kind of expected to act and operate as *the* expert. Not only that, but the db I'm attempting to design is pretty critical to the back-end operations of a biz that's about to burst wide open with customers. In a nutshell, there's a lot of pressure being applied. I know an aweful lot of what ya'll call "basics", but that will only get me so far. I honestly believe that I understand the concepts behind normalization and the various degrees of normal form. Where I'm at now is the actual implementation...the physical.I read that article about a week ago along with another that I found linked here about the "official" approach to normalization. It cleared up a lot of misconceptions I had about db design.Another thing I should mention is that I'm rar'in to go here. I read an aweful lot and search these forums frequently. I'm not bucking your suggestion, rather I've read so much that I'm anxious to put these concepts to the test with real code...the "right" way.-Brian |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-06-24 : 18:39:49
|
| Brian -- give us more information on the table itself -- what is the data you are trying to store in it? That would probably help alot with giving you some advice. sample data would help out greatly as well.- Jeff |
|
|
|
|
Primary Key's