January 6, 2006 at 12:51 pm
Hello,
I am running Power Builder as a front end. Once a week my table is corrupted with a primary key violation even thou I have a primary key set on the table.
QUESTION: Why is SQL Server allowing duplicate keys into my table without checking the primary key constraint?
I have a SQL Server 2000 SP3 Database running on Windows 2000 Server.
Following is the table structure:
CREATE TABLE [dbo].[XXXXX] (
[PEOPLE_CODE] [varchar] (1) NOT NULL ,
[PEOPLE_ID] [varchar] (9) NOT NULL ,
[PEOPLE_CODE_ID] [varchar] (10) NOT NULL ,
[ACADEMIC_YEAR] [varchar] (4) NOT NULL ,
[ACADEMIC_TERM] [varchar] (10) NOT NULL ,
[ACADEMIC_SESSION] [varchar] (10) NOT NULL ,
[PROGRAM] [varchar] (6) NOT NULL ,
[DEGREE] [varchar] (6) NOT NULL ,
[CURRICULUM] [varchar] (6) NOT NULL ,
[COLLEGE] [varchar] (6) NOT NULL ,
[DEPARTMENT] [varchar] (6) NOT NULL ,
[CLASS_LEVEL] [varchar] (4) NOT NULL ,
[NONTRAD_PROGRAM] [varchar] (6) NOT NULL ,
[POPULATION] [varchar] (6) NOT NULL ,
[ADVISOR] [varchar] (10) NULL ,
[ADMIT_YEAR] [varchar] (4) NULL ,
[ADMIT_TERM] [varchar] (10) NULL ,
[ADMIT_SESSION] [varchar] (10) NULL ,
[ADMIT_DATE] [datetime] NULL ,
[MATRIC] [varchar] (1) NULL ,
[MATRIC_YEAR] [varchar] (4) NULL ,
[MATRIC_TERM] [varchar] (10) NULL ,
[MATRIC_SESSION] [varchar] (10) NULL ,
[MATRIC_DATE] [datetime] NULL ,
[FULL_PART] [varchar] (4) NULL ,
[ACADEMIC_STANDING] [varchar] (6) NULL ,
[REGISTER_LIMIT] [numeric](6, 3) NOT NULL ,
[EXPECT_GRAD_MM] [varchar] (2) NULL ,
[EXPECT_GRAD_YYYY] [varchar] (4) NULL ,
[ENROLL_SEPARATION] [varchar] (4) NULL ,
[SEPARATION_DATE] [datetime] NULL ,
[CREDITS] [numeric](6, 3) NOT NULL ,
[CREATE_DATE] [datetime] NOT NULL ,
[CREATE_TIME] [datetime] NOT NULL ,
[CREATE_OPID] [varchar] (8) NOT NULL ,
[CREATE_TERMINAL] [varchar] (4) NOT NULL ,
[REVISION_DATE] [datetime] NOT NULL ,
[REVISION_TIME] [datetime] NOT NULL ,
[REVISION_OPID] [varchar] (8) NOT NULL ,
[REVISION_TERMINAL] [varchar] (4) NOT NULL ,
[ABT_JOIN] [varchar] (1) NOT NULL ,
[PREREG_VALIDATE] [varchar] (1) NOT NULL ,
[PREREG_VAL_WHO] [varchar] (8) NULL ,
[PREREG_VAL_DATE] [datetime] NULL ,
[REG_VALIDATE] [varchar] (1) NOT NULL ,
[REG_VAL_WHO] [varchar] (8) NULL ,
[REG_VAL_DATE] [datetime] NULL ,
[GRADUATED] [varchar] (1) NOT NULL ,
[GRADUATED_YEAR] [varchar] (4) NULL ,
[GRADUATED_TERM] [varchar] (10) NULL ,
[GRADUATED_SESSION] [varchar] (10) NULL ,
[ORG_CODE_ID] [varchar] (10) NOT NULL ,
[ACADEMIC_FLAG] [varchar] (1) NOT NULL ,
[APPLICATION_FLAG] [varchar] (1) NOT NULL ,
[APP_STATUS] [varchar] (4) NULL ,
[APP_STATUS_DATE] [datetime] NULL ,
[APP_DECISION] [varchar] (4) NULL ,
[APP_DECISION_DATE] [datetime] NULL ,
[COUNSELOR] [varchar] (10) NULL ,
[COLLEGE_ATTEND] [varchar] (4) NULL ,
[ACADEMIC_RATING] [varchar] (6) NULL ,
[ADVOCATE] [varchar] (10) NULL ,
[ACA_PLAN_SETUP] [varchar] (1) NOT NULL ,
[STATUS] [varchar] (1) NOT NULL ,
[TRANSCRIPT_SEQ] [varchar] (3) NOT NULL ,
[LAST_ACTIVITY] [varchar] (1) NOT NULL ,
[CURRENT_ACTIVITY] [varchar] (1) NOT NULL ,
[INQUIRY_FLAG] [varchar] (1) NOT NULL ,
[INQUIRY_DATE] [datetime] NULL ,
[FIN_AID_CANDIDATE] [varchar] (1) NULL ,
[EXTRA_CURRICULAR] [varchar] (1) NULL ,
[DEGREE_CANDIDATE] [varchar] (1) NULL ,
[INTEREST_LEVEL] [varchar] (4) NULL ,
[INQ_STATUS] [varchar] (4) NULL ,
[INQ_STATUS_DATE] [datetime] NULL ,
[APPLICATION_DATE] [datetime] NULL
) ON [PRIMARY]
GO
Following is the Primary Key constraint:
ALTER TABLE [dbo].[XXXXX] ADD
CONSTRAINT [XXXXX_PK] PRIMARY KEY CLUSTERED
(
[PEOPLE_CODE_ID],
[ACADEMIC_YEAR],
[ACADEMIC_TERM],
[ACADEMIC_SESSION],
[PROGRAM],
[DEGREE],
[CURRICULUM]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
Thanks In advance for you help!!
January 9, 2006 at 5:31 am
The table name on your CREATE TABLE and ALTER TABLE statements are different. Do you have the table defined twice with different names on your system?
January 9, 2006 at 11:38 am
Would like to share my experience with you...Not sure if you are having similar situation
SQL server has the problem......
While making a RPC call to SQL server, even you can insert junk values in your sql table ie. You can cross the limit of specific data type.
trying using RPC calls (Query analyzer is not RPC call)
Thanks,
-Nirmal.
SQL Server DBA/Oracle DBA/Solaris Admin.
January 9, 2006 at 9:42 pm
H!!!,
This is a composite primary key which is created for a combination of seven columns ,in this scenarion the combination of seven columns ie PEOPLE_CODE_ID,ACADEMIC_YEAR
ACADEMIC_TERM,ACADEMIC_SESSION,PROGRAM,DEGREE and CURRICULUM of a condidate will be unique.When you open the table XXXXX you will get duplicate values in PEOPLE_CODE_ID or anyother columns but the combination of seven columns will be unique .I think the point is clear now!!!
regards,
Vinod S.R (DBA)
HTC Global Services Chennai
09840856202
ALTER TABLE [dbo].[XXXXX] ADD
CONSTRAINT [XXXXX_PK] PRIMARY KEY CLUSTERED
(
[PEOPLE_CODE_ID],
[ACADEMIC_YEAR],
[ACADEMIC_TERM],
[ACADEMIC_SESSION],
[PROGRAM],
[DEGREE],
[CURRICULUM]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
January 9, 2006 at 9:56 pm
When you say "corrupt" - what is telling you it is corrupt and giving you the primary key violation error? If it is your powerbuilder client-side app, then SQL is throwing the error back when you try to insert some duplicate data. In this case, your primary key is being observed faithfully - your logic must be wrong and you are inserting duplicate data (perhaps you need to check if it exists and update it rather than just blindly insert the data).
If you are getting your error message from something else (like some SQL Server log file for example), please let us know where you got the message and some examples of what is happening when the message appears.... I suspect though that it is the situation i described in the previous paragraph.
January 10, 2006 at 6:48 am
Reply to Ian Yates:
I get error message that says following First:
Table error: Database ID 5, object ID 1314869659, index ID 0. Chain linkage mismatch. (1:469714)->next = (1:345947), but (1:345947)->prev = (1:345946)..
Then I research and I find out that it is the table XXXXXX that is corrupted. I try to drop and rebuild each index and then I find out that there is duplicate data in the Primary Key and it will not let me rebuild the primary key until i fix the data first by removing duplicate rows.
The application never gets any error message when it is trying to insert duplicate data that is what I think is a strange behavior on SQL Server Side. Once the application has inserted a duplicate data buy USER X it is ok and then when USER Y tries to use the same table it will hit the Primary key and give you a linkage error.
Thanks for your help!!!! I appreciate it very much....
January 10, 2006 at 6:52 am
Reply to Vinod:
Vinod, I don not get primary key violation on individual field. I get primary key violation on the combination of the fields.
As you can see from my post reply, after I find out that my table is corrupted I try to drop all the indexes and rebuild them. When I try to rebuild the primary key index it tells me that I have duplicate data using the combination of the fields and I can not rebuild the index.
Thanks for your help!!!!
January 11, 2006 at 3:37 pm
Ahhh ok - thanks for posting the error message. This makes it very clear what your problem is (well at least less ambiguous )... Have you checked your disks for errors? It appears that you are suffering from some sort of corruption on a regular basis so I would check
a) Ensure you have the latest drivers for your disk subsystem (RAID controllers, etc)
b) Upgrade to the latest SQL service pack
c) Use the SQL IO stress tool on your disks
see http://www.microsoft.com/sql/prodinfo/previousversions/scalability.mspx (bottom of the page) - this may help you diagnose some hardware issues...
January 26, 2006 at 12:25 pm
I've gotten those duplicates too.
In this case it was a emergency recovery and the indexes were partially corrupted.
Solved by dropping all indexes, running a DBCC CHECKTABLE, removing the duplicates (in my case all of a certain dateinterval) en reapplying all indexes.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply