Can't change the contents of a table in SQL Server2005

Hi,
I have a problem that in a database (named DDS) i can change or delete records in all tables but there is one which i can't. There isn't any foreigh keys(There is one dependencies, a view). So could you please tell me that is there any way that while designing a database in SQL Server we can lock a table and is there any way to open it and change the values of a field. There are two constraints as:

USE [DSS]
GO
ALTER TABLE [dbo].[tblGlob] DROP CONSTRAINT [DF_tblGlob_BYDEFT]

USE [DSS]
GO
ALTER TABLE [dbo].[tblGlob] DROP CONSTRAINT [DF_tblGlob_SetValue]

Even if i delete these constaints, still i can't change the data in this table.
Here is the image of error i get:-
Attachment 96739

The login has all rights for that database.
Is there any one who knows how to do this.

Ayyaz

It sounds like either you don't have a primary key or you are changing the PK value to the same as another row.

I checked there is no primary key for this table. No triggers, only those two CONSTAINTS which i have mentioned above, on those two fields( BYDEFT & SETVALUE, there data type is bit). Basically i am trying to change a field named COMPANY_ADDRESS but i wont let me do this. Could you please also tell me what is the meaning of these two constraints.
Ayyaz

please i need quick help.
Ayyaz

Why is there no primary key? It's a rare thing that it's appropriate for a table not to have a primary key. It's especially rare when you want to make changes to the data in that table. How exactly do you expect to uniquely identify a record in order to update it if you don't have a primary key?

Here is the DDL for this table:

USE [DSS]
GO
/****** Object: Table [dbo].[tblGlob] Script Date: 02/21/2013 14:09:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblGlob](
[PrinterName] [varchar](50) NULL,
[GlobalExpiry] [varchar](50) NULL,
[DBSTATUS] [bit] NULL,
[BYDEFT] [bit] NULL,
[CompanyName] [varchar](200) NULL,
[CompanyAddress] [varchar](200) NULL,
[SettingName] [varchar](500) NULL,
[SetValue] [bit] NULL,
[Organization] [varchar](200) NULL,
[Phone] [varchar](50) NULL,
[MODIFIER] [varchar](50) NULL,
[LAST_MODIFIED] [datetime] NULL,
[OPERATION_TYPE] [varchar](50) NULL,
[BankName] [varchar](500) NULL,
[Logo] [image] NULL,
[Email] [varchar](200) NULL,
[Website] [varchar](200) NULL,
[EmailServer] [varchar](500) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

The questions I asked in my previous post were not rhetorical, i.e. I asked them because I wanted answers to them.

http://geekswithblogs.net/allensb/ar.../27/86484.aspx
http://social.msdn.microsoft.com/For...6-d94aa0dac9b7

Just google for "either do not make the row unique" :cool:

Given that there is no PKey ... if what we are to believe what we are told ... I wonder if there is another table that is using CompanyAddress as a foreign key to this table... as a result it is acting as a pseudo pkey. Or.... keep reading, another thought occurred...

The two "constraints" you dropped are not constraints at all... they are defaults... they set the default value of a couple of fields for cases when no specific value is given... dropping them does no good, and could potentially do harm as now they are lost... hopefully you scripted their definition somewhere before hand so you can add them back in.

What you need to be looking for is not constraints but indexes... It's possible there is an index with a unique and/or clustered index on the field...

-tg

I'm pretty sure this is a "bug" in SqL Server. (I'm not sure it's truly a bug but if it's WAD then the D is ropey, IMO). I know it existed in 2005, not sure after that.

If you have no primary key on a table then SQL Server can only tell the difference between the records based on the complete set of field values. It does't reliably maintain some underlying unique ID as you might expect. That's fine... right up until you have two records in the heap that have exactly the same set of values. Once you create that situation SQL Server can no longer uniquely identify the records. Even that's not a serious problem until you come to do an update at which point the update mechanism can't work out which record to update and it barfs. You'd think it would update both if they meet the criteria specified in the where clause but it doesn't, it just barfs. You can't delete the records either, for the same reason. Neither can you creaet a primary key retrospectively becasue the records aren't, of course, unique.

The only solution I found when I got bit by this a few years ago was to:-
1. Script the table for create, making sure you sript and indexes, constraints etc.
2. Do a Select unique * Into CopyTable (The unique will remove the duplicates)
3. Drop the original table
4. Recreate it using your scripts
5. Copy the data back again using an Insert Into
6. Even if you genuinely intended the records not to be unique, give the table an arbitrary primary key anyway so you don't get stung again

Yeah the issue is you have a identical rows with no primary key. I'd probably see if it was on or two, if so, drop them with a t-sql script, add a primary key field, and then do your alter table...

Quote:

---

Originally Posted by FunkyDexter

I'm pretty sure this is a "bug" in SqL Server. (I'm not sure it's truly a bug but if it's WAD then the D is ropey, IMO). I know it existed in 2005, not sure after that.

If you have no primary key on a table then SQL Server can only tell the difference between the records based on the complete set of field values. It does't reliably maintain some underlying unique ID as you might expect. That's fine... right up until you have two records in the heap that have exactly the same set of values. Once you create that situation SQL Server can no longer uniquely identify the records. Even that's not a serious problem until you come to do an update at which point the update mechanism can't work out which record to update and it barfs. You'd think it would update both if they meet the criteria specified in the where clause but it doesn't, it just barfs. You can't delete the records either, for the same reason. Neither can you creaet a primary key retrospectively becasue the records aren't, of course, unique.

The only solution I found when I got bit by this a few years ago was to:-
1. Script the table for create, making sure you sript and indexes, constraints etc.
2. Do a Select unique * Into CopyTable (The unique will remove the duplicates)
3. Drop the original table
4. Recreate it using your scripts
5. Copy the data back again using an Insert Into
6. Even if you genuinely intended the records not to be unique, give the table an arbitrary primary key anyway so you don't get stung again

---

I would second this except the indexes. I would add all indexes(except PK) afterwards. I always do that because most of the time I move a lot of data and populating a table with lots of data is slow. Adding indexes afterwards is actually faster. I've done this exercise many times with millions of records. If you create your table with indexes right of the bat, the inserts are a lot slower as it has to physically reorder every insert. Other than that, a good advice.

Quote:

---

Originally Posted by Serge

I would second this except the indexes. I would add all indexes(except PK) afterwards. I always do that because most of the time I move a lot of data and populating a table with lots of data is slow. Adding indexes afterwards is actually faster. I've done this exercise many times with millions of records. If you create your table with indexes right of the bat, the inserts are a lot slower as it has to physically reorder every insert. Other than that, a good advice.

---

ONLY if they are clustered indexes... which should only be based on a single, or two fields tops... but non-clustered indexes should have minimal, if any impact. I rarely use clustered indexes, unless there is a specific index that would benefit greatly from it... other than that, I index fields which tend to get used in where clauses, or in lookups... then create covering indexes which includes multiple fields.

Example: If I have a table where I've got two fields that keep coming up in where clauses, and sometimes I have one, sometimes I have the other, and sometimes I have both... I'll create an index on each field individually, then create an index on top of that with both fields.

-tg