March 15, 2011 at 8:45 am
Hi All!
I got some good help on changing fields the other day.
My problem now is the ID field.
It is (I think good practise!) made as an IDENTITY(1,1).
Today it is:
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
My customer want it as:
[ID] [numeric](18, 0) NOT NULL,
It must be something with:
ALTER TABLE [3NF_Medarbejder]
ALTER COLUMN [ID] [numeric](18, 0) NOT NULL
But this is not enough
Best regards
Edvard Korsbæk
March 15, 2011 at 8:55 am
Why does your customer care if it is identity or not? Micro managing customers are the worst.
Unfortuantely you can't just remove the identity property directly. You will have to create a new column and populate it with the current values and then drop the current column. Last but not least you will then have to rename the temp column. Of course if the current column is your primary key you will have to drop the key and recreate it on the new column. You can use SSMS in table design mode and then capture the script of changes and with a little modifying it will work for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 15, 2011 at 10:22 am
Before making the change that the customer is requesting.
As an IDENTITY the each value is unique.
1. What would be the possible adverse effects if duplicate values are inserted.
2. Is it utilized as a foreign key in another table?
March 15, 2011 at 10:42 am
bitbucket-25253 (3/15/2011)
Before making the change that the customer is requesting.As an IDENTITY the each value is unique.
Unless a column with an IDENTITY is defined as a primary key or a unique index, it is NOT unique. Is this what you meant?
Just stop by my office about 3 or 4 times a week when I have to fix an improperly inserted value in an IDENTITY column.
Don't ask, just suffice to say I'm keeping an old, buggy, soon to be replaced system alive for the next 60 days.
I do agree that there appears to be no need to remove the identity. Does the client know what they are asking, and understand the ramifications?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 15, 2011 at 11:24 am
a column with an IDENTITY
yes each value is unique wether it is defined as a primary key, index or whatever.
March 15, 2011 at 11:39 am
I have app. 150 tables in the DB, which i control and uses.
The customer has two tables, which they use as their link to my db.
Its their task to fill it - I use it as a look-up table, and i do not use the ID anywhere.
And, yes, I am sure they know what they do. I am a newbie to SQL DB's - Thats why I have to ask so much! - and they maintain some of the the primary databases used by the danish state.
And they claim that they cannot fill the table correctly if I have the idendity(1,1) on the id field.
So, I do as said - And thanks for your help. I really could not find a way to do it - As you have showed me, because it is impossible.
Best and very humble regards
Edvard Korsbæk
March 16, 2011 at 12:33 pm
edvard 19773 (3/15/2011)
I have app. 150 tables in the DB, which i control and uses.The customer has two tables, which they use as their link to my db.
And they claim that they cannot fill the table correctly if I have the idendity(1,1) on the id field.
It sounds to me that they're trying to overwrite the ID column with other information. If they need ID for something, then create a new column for them that they can populate with their identifying information.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply