Home » SQL & PL/SQL » SQL & PL/SQL » ORA-02437 (oracle 10g)
ORA-02437 [message #413376] Wed, 15 July 2009 07:50 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Hi,my table is like this
CREATE TABLE EMRRoleFeatureMatrix (
	ROLE_FEATURE_ID numeric(20, 0) NOT NULL ,
	EMR_FEATURE_ID numeric(20, 0) NOT NULL ,
	ROLE_ID numeric(20, 0) NOT NULL ,
	ACCESSIBILITY NUMBER(1,0),
	GROUP_ID NUMBER(20, 0),
	NO_CHANGE NUMBER(1, 0)
)
/

i have inserted values into it the thing is i got duplicate values into it i.e like 857 for role_feature_id got repeated and now lkie there are 900 records.if i give distinct for that column i am getting 690 records.

when i used command like this
ALTER TABLE EMRRoleFeatureMatrix ADD CONSTRAINT "PK_EMRRoleFeatureMatrix" PRIMARY KEY (ROLE_FEATURE_ID )
Error report:
SQL Error: ORA-02437: cannot validate (TEST12.PK_EMRRoleFeatureMatrix) - primary key violated
02437. 00000 -  "cannot validate (%s.%s) - primary key violated"
*Cause:    attempted to validate a primary key with duplicate values or null
           values.
*Action:   remove the duplicates and null values before enabling a primary
           key.


now how can i give primary key constarint and how i remove those duplicates
Re: ORA-02437 [message #413377 is a reply to message #413376] Wed, 15 July 2009 07:55 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
delete the duplicates
Re: ORA-02437 [message #413380 is a reply to message #413376] Wed, 15 July 2009 07:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CODE tags is ONLY for code or if you want to use it for other stuff then manually split your lines at 80 characters.
In ALL cases, Use the "Preview Message" button to verify.

Regards
Michel
Re: ORA-02437 [message #413382 is a reply to message #413377] Wed, 15 July 2009 08:03 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
how can i do that
Re: ORA-02437 [message #413383 is a reply to message #413382] Wed, 15 July 2009 08:04 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
rajasekhar857 wrote on Wed, 15 July 2009 09:03
how can i do that


Yeesh, please search either here or google. Do you know what you should search for?

I would have to say, it is most likely the most common asked question from a beginner.

[Updated on: Wed, 15 July 2009 08:06]

Report message to a moderator

Re: ORA-02437 [message #413386 is a reply to message #413383] Wed, 15 July 2009 08:24 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
there were no duplicates or null values but i am getting this error
Re: ORA-02437 [message #413391 is a reply to message #413386] Wed, 15 July 2009 08:30 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
rajasekhar857 wrote on Wed, 15 July 2009 09:24
there were no duplicates or null values but i am getting this error


but alas, you also said:

Quote:
for role_feature_id got repeated and now lkie there are 900 records.if i give distinct for that column i am getting 690 records


So what is it? Based on your posts, you really need to think out your question before you post them.
Re: ORA-02437 [message #413395 is a reply to message #413386] Wed, 15 July 2009 08:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To find the duplicates:
SELECT role_feature_id,count(*)
FROM EMRRoleFeatureMatrix
HAVING count(*) > 1
GROUP BY role_feature_id; 


If all the rows for each role_feature_id are duplicates of each other, then you can delete the duplicate with something like
DELETE EMRRoleFeatureMatrix
WHERE rowid not in (select min(rowid)
                    from   EMRRoleFeatureMatrix
                    group by role_feature_id)
                    


If the rows for each role_feature_id have different values, then you'll need to decide which ones to keep.
Re: ORA-02437 [message #413402 is a reply to message #413395] Wed, 15 July 2009 09:55 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
thanks JRowbottom for your response

[Updated on: Wed, 15 July 2009 09:57]

Report message to a moderator

Re: ORA-02437 [message #413578 is a reply to message #413376] Thu, 16 July 2009 08:43 Go to previous messageGo to next message
ganludong
Messages: 5
Registered: July 2009
Location: SICHUAN NORMAL UNIVERSITY...
Junior Member
you have build a primary key


action:

1> alter table .. drop primey key..
2> alter table .. add constraint primary key(new-primary key,old-primary key list)
Re: ORA-02437 [message #413584 is a reply to message #413578] Thu, 16 July 2009 09:00 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
ganludong wrote on Thu, 16 July 2009 14:43

2> alter table .. add constraint primary key(new-primary key,old-primary key list)


What does that mean?
Previous Topic: IS NULL and ='NULL'
Next Topic: ORA-06550: line 1, column 8: PLS-00103
Goto Forum:
  


Current Time: Sat Dec 03 14:07:06 CST 2016

Total time taken to generate the page: 0.12764 seconds