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.
| Author |
Topic |
|
cgig
Starting Member
2 Posts |
 Posted - 2009-05-18 : 14:51:08
|
| I have a primary key: ClientID, int and is identity.I have another column, postcode,in this table where I save strings of this format: 1111AA. This column is not a primary key.I have finally a third column, Number, where I save string of this format: [0,9]I want to generate automatically a value for the primary key of the clientID which would consist of a contanation between the postcode column and the Number column. Say postcode is 1111AA and Numer is 8, the generated clientID should be 1118, as integer!Can anyone help me?thanks in advance! |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2009-05-18 : 15:13:58
|
| My own curiousity wonders why you would want to do this, but if you are certain that you do, here are some of the things you need to do...1) Remove the IDENTITY property from the ClientID field since you are going to manage the values yourself.2) Define the rules for the value. Your example suggests you are taking the first three characters of the postcode and concatenating the Number, but is that exactly the rule? Why not take the first four characters? Also, what should happen if the postcode or Number changes? Should the ClientID also be updated? If so, that suggests the potential for a huge amount of cascading updates becoming necessary.3) Once you have your rule rock-solid, you need a mechanism for updating the ClientID. You might be able to do this with a Default. I have only used constants as default values and haven't tried a formula. Another option would be an INSERT trigger that would update the Client ID. Or an INSERT and Update trigger if ClientID changes as I asked in point #2 above.--------------------------------------------Brand yourself at EmeraldCityDomains.com |
 |
|
|
|
|
|
|
|
Identity column formula based on 2 other columns