CREATE UNIQUE INDEX terminated
-
i have made a new tabel with data from old table and a new PK. Now i try to get access to change data in an row, which have been faulty, but i get this message :CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '1'. could not create constraint.
I'm using SQL enterprise manager. How can i get that damned key on my ID row, so i can correct my data?. I Don't have dublicate keys.
When i try to put it on, it creates index which seems ok, but the fault i sstill coming and i can't save. Can anybody help?
-
select yourkeycolum(s) , count(*) as counter
from your_object
group by yourkeycolum(s)
having count(*) > 1
-- order by yourkeycolum(s)
I hope this helps
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
To explain it. You apparently *do* have duplicates in your data. Run the above script, remove the duplicates which will show up there and then you should be able to create your index.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks for the addition, Frank.
I was to much in a hurry for my lunchbreak.
.... and lost the "novice"-info on the left ... btw Welcome Erik
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
tried to delete a row with possible troubles, but get message:
key column information insufficient or incorrect. too many rows were affected by update.
same mesage af if i try to edit any cell.
It doesn't helped with the suggestion.
nice try. at least here's some who responds!
any other ideas?
-
check this site's search : "remove duplicates".
Test it before going live !
Many of us use Query Analyzer to perform this kind of stuff.
(Enterprise manager / Tools / SQL Query Analyzer)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
key column information insufficient or incorrect. too many rows were affected by update.
You have not defined a PRIMARY KEY constraint on your table!
To workaround this, open the table in Enterprise Manager, add another column, make it integer, define IDENTITY on it and save the modification. Then remove the duplicates (shouldn't be a problem right now). And finally remove the newly added column. At last look at the structure of your table, identify the primary key and define the PK constraint on it.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
i used query analyser for it and it gave the results i wrote before. trying if i can find anything about this, it takes a little without searchpanels!
-
You have read my post, haven't you?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
This really snap it's own tail!
It was the problem from start: i couldn't put new identity column on the table. Gives this: 'hovedtabel' table- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column 'newid' cannot be added to table 'hovedtabel' because it does not allow nulls and does not specify a DEFAULT definition.
Yes, i read and read and are in more forums with the troubles.
-
It would be helpful if you could post the statement that caused this error.
Do you mean, you can't do
ALTER TABLE < mytable >
ADD < myid > INT NOT NULL IDENTITY
GO
???
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
result of insertion into queryanalyser:
Multiple identity columns specified for table 'hovedtabel'. Only one identity column per table is allowed.
don't understand it, in index/keys there's only one specified? where are the others?
-
EnterpriseManagers does not autorefresh.
If you don't trust its results, disconnect and connect again so it refreshes all catalog-data.
Hitting F5 at the tables-tree in the lefthand pain, also may help.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
The error message is pretty much self-describing. You can hava only *one* IDENTITY column per table. I think you should provide more information on your table structure. http://www.aspfaq.com/etiquette.asp?id=5006
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
INSERT INTO [PLVKTSYS].[vktadmin].[Hovedtabel2]([vkt_nr], [billede], [fil1], [fil2], [gruppe], [undergruppe], [ops_overvkt], [ops_undervkt], [anvendelse], [bem_t_anv], [totallaengde], [delinger], [bredde], [hoejde], [naeseRadius], [hornimm], [vinkel], [sporbredde], [forsætn_prof_hoejde], [tegningshenvisning], [placering], [reol], [hylde], [brugesaf], [kunde], [leverandor], [rekvnr_pris], [bem_t_vaerk], [leveringsdato], [udgaaet], [udgaaetdato], [ejer], [betalingsandel], [aktiv], [sidstekontrol], [notat])
VALUES ('vkt_nr,decimal(18,1)', 'billede,nvarchar(255)', 'fil1,nvarchar(255)', 'fil2,nvarchar(255)', 'gruppe,int', 'undergruppe,int', 'ops_overvkt,int', 'ops_undervkt,int', 'anvendelse,int', 'bem_ t_ anv,int', 'totallaengde,int', 'delinger,nvarchar(110)', 'bredde,real', 'hoejde,real', 'naeseRadius,real', 'hornimm,nvarchar(50)', 'vinkel,nvarchar(50)', 'sporbredde,nvarchar(50)', 'forsætn_prof_hoejde,int', 'tegningshenvisning,nvarchar(255)', 'placering,int', 'reol,nvarchar(50)', 'hylde,nvarchar(50)', 'brugesaf,nvarchar(50)', 'kunde,int', 'leverandor,int', 'rekvnr_ pris,nvarchar(50)', 'bem_ t_ vaerk,int', 'leveringsdato,datetime', 'udgaaet,bit', 'udgaaetdato,nvarchar(50)', 'ejer,int', 'betalingsandel,nvarchar(50)', 'aktiv,int', 'sidstekontrol,int', 'notat,int')
Gives nothing.
I made a new Table with these attributes and an identifier, but no data. I ran this into the sql-query analyser.
The site from vyansk isn't quite understandable, if just i could catch his specific example 1 - he says it can transfer data.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply