How to enable primary key on a column having duplicate values in oracle database

You may come across a situation where you have to put Primary key on a column in table that is having duplicate rows and you cannot delete the duplicate rows.

Now this is a tricky situation.....below is the solution for the above problem.

for example you have to put primary key on "B column" "on ABC table" that is having duplicate values.

1) create index ABC_B_INX on B tablespace ABC;

2) alter table ABC add constraint ABC_B_PK primary key(B) disable;

3) alter table ABC enable novalidate constraint ABC_B_PK;

Hope this helps.....

How to check last ddl on a table in Oracle Database

drop table test;

CREATE TABLE test (
testcol VARCHAR2(20))
ROWDEPENDENCIES;

SELECT table_name, dependencies
FROM user_tables where table_name='TEST';

SELECT current_scn
FROM v$database;

INSERT INTO test VALUES ('ABC');

COMMIT;

INSERT INTO test VALUES ('ABC');

COMMIT;

INSERT INTO test VALUES ('ABC');

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

SELECT current_scn
FROM v$database;

UPDATE test
SET testcol = 'DEF'
WHERE rownum = 1;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

UPDATE test
SET testcol = 'XYZ';

SELECT ORA_ROWSCN, rowid, testcol FROM test;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

CREATE TABLE test2 AS
SELECT * FROM test;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test2;

INSERT INTO test VALUES ('ABC');

UPDATE test SET testcol = 'DEF' WHERE rownum = 1;

UPDATE test2 SET testcol = 'GHI' WHERE rownum = 1;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

SELECT scn_to_timestamp(ORA_ROWSCN), rowid, testcol FROM test2;

SELECT scn_to_timestamp(ORA_ROWSCN) FROM test2;

SELECT current_scn
FROM v$database;

Keep observing the output of the above select queries.