June 29, 2006 at 5:19 pm
Hi there,
I have a problem adding a primary key in table even though when I look into the sqlmanager I've seen no primary key defined. Below is sql statement and also the error.
IF NOT EXISTS (SELECT so.id, so.name, si.name
FROM sysobjects AS so
INNER JOIN sysindexes AS si ON (so.id = si.id)
WHERE so.xtype = 'U'
AND so.name = 'report_layout'
AND si.indid = 1)
BEGIN
ALTER TABLE report_layout ADD PRIMARY KEY (id)
END
server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '42400'.
Server: Msg 1750, Level 16, State 1, Line 8
Could not create constraint. See previous errors.
The statement has been terminated.
Your help is greatly appreciated.
Thanks
June 29, 2006 at 5:58 pm
Just whipping out the old crystal ball, I'd say you have at least one duplicate id number in the table, and since primary keys have to be unique, it can't complete the indexing operation.
if you run
select count(*) from report_layout where id = 42400
and the result is greater than 1, you'll have to eliminate the duplicates before continuing, or define a non-unique index instead of a primary key.
June 29, 2006 at 6:08 pm
Run this:
SELECT Id, count(*)
FROM report_layout
GROUP BY Id
HAVING COUNT(*) > 1
It will show you how bad is you situation with duplicate key.
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply