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 |
|
vali1005
Starting Member
6 Posts |
 Posted - 2008-03-27 : 17:47:12
|
| Hello everyone!Even though I am not "new to SQL Server", my experience in working with it has ebbed and flowed, and working with SQL Server is not something that happens for me even on a monthly basis.I work as an in-house programmer for a financial investment company and I have been asked to work on a database that is to hold financial data.One of the requirements for the tables has been that the Primary Key should be [name_of_the_table] + [Identity].So, for example, for table "tblEquities", the values of the Primary Key would be:PK-------tblEquities1tblEquities2tblEquities3tblEquities4tblEquities5My questions are:1) Is it possible to achieve this by using just one column, as in defining a formula for the "Computed Column Specification" , something that would look like "'tblEquities' + Identity()"?2) If the above is not possible, I assume it's much easier to just define a column as being Identity column and then create a 2nd computed column that would aggregate the name of the table with the value in the Identity column, so that I end up with a column holding the desired values? (I guess it would look something like "'tblEquities' + [Identity]")Also, I have been asked to find out what SQL 2005 have/offers, in term of guaranteeing referential integrity. Are the "Database Diagram", as well as the "Relationships" dialog box, the tools that would meet that criteria or does SQL 2005 contain other tools that would help in this regard?Thank you! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-27 : 18:57:39
|
| This is a horrible idea. If you must have that as a primary key, then I'd would highly recommend just using the identity column and then appending the column name in your queries.Referential integrity is guaranteed if you use foreign keys.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-03-27 : 19:25:19
|
| I second that. That's probably one of the worst design decisions I've ever heard of. There is absolutely no reason to prefix data with table names. Prefixing column names is bad enough. Are you sure you understand this spec right? Are you sure they are not referring to the name of the columns in the table, not the values in the data? What you have described is literally almost ... well, to be honest .... insane!- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
vali1005
Starting Member
6 Posts |
Posted - 2008-03-27 : 19:53:20
|
| I explained the difficulties of achieving that specific requirement and a decision has been made to go with "basic" Primary Keys, i.e. Identity columns. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-27 : 20:58:40
|
| We aren't disagreeing about the identity column decision. What we are disagreeing with is prefixing it with the table name. That doesn't make any sense. What makes sense is naming the primary key constraint TableNameId or similar.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
manmadhu
Starting Member
3 Posts |
Posted - 2009-01-15 : 12:24:45
|
| Did you find a solution to this? I have the same requirement in my SPEC. The need a table, where primary key is TABLENAME + 'Serial Identifier'. They also wanted the datatype as Integer. Is this possible?Thanks,Kreshiv. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-01-15 : 12:55:45
|
| Did you not read the responses about how stupid an idea this is?________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
|
|
|
|
|
SQL 2005: creating custom Primary Key