[Oracle] Drop primary key probleem

[mdcroon]

Ik krijg een raar probleem met het droppen en opnieuw aanmaken van een primary key. Volgens onderstaand script maak ik een primary key aan op een bestaande tabel:

code:

ALTER TABLE uclass
ADD CONSTRAINT pk_uclass
PRIMARY KEY (class_id);

Vervolgens drop ik de primary key:

code:

ALTER TABLE uclass DROP CONSTRAINT pk_uclass;

Ik maak opnieuw een primary key aan:

code:

ALTER TABLE uclass
ADD CONSTRAINT pk_uclass
PRIMARY KEY (class_id);

Op dit moment krijg ik de melding "name is already used by an existing object". Voer ik een select uit op de all_indexes tabel dan zie ik inderdaad nog een record staan. Ik kan nu dus geen nieuwe primary key aanmaken.

Kan iemand dit gedrag verklaren en hoe ik het probleem moet oplossen?

Het gaat om Oracle versie 10g.

[ Voor 2% gewijzigd door mdcroon op 27-06-2007 10:51 . Reden: Oracle versie toegevoegd ]

[BtM909]

Commit je je changes wel ?

[Marcj]

Krijg je bij het droppen van de primairy key ook geen warning of iets dergelijks? Want blijkbaar wordt de key gewoon niet gedropped. En dit kan natuurlijk verschillende oorzaken hebben.

[mdcroon]

Ja, tussentijds wordt inderdaad een commit uitgevoerd.

[mdcroon]

Marcj schreef op woensdag 27 juni 2007 @ 10:52:
Krijg je bij het droppen van de primairy key ook geen warning of iets dergelijks?

De melding die ik krijg is "Table altered"

[justmental]

Als dit de exacte statements die je gebruikt hebt zijn, dan zou het moeten werken:

code:

SQL> create table uclass (class_id number);

Table created.

SQL> ALTER TABLE uclass
  2  ADD CONSTRAINT pk_uclass
  3  PRIMARY KEY (class_id);

Table altered.

SQL> ALTER TABLE uclass DROP CONSTRAINT pk_uclass;

Table altered.

SQL> ALTER TABLE uclass
  2  ADD CONSTRAINT pk_uclass
  3  PRIMARY KEY (class_id);

Table altered.

Als de pk gemaakt is met een 'using index' clausule dan zijn de constraint en index ontkoppeld en zul je hem ook weer met een 'using index' aan moeten maken.

[mdcroon]

Het is opgelost maar begrijpen doe ik het nog niet.

De tabel is aangemaakt in tablespace x. De primary key stond in tablespace y. Blijkbaar blijft de primary key bestaan indien deze in een andere tablespace zit.

Wat ik nu heb gedaan is de tabel gedropt. Vervolgens heb ik de tabel en de primary key opnieuw aangemaakt in dezelfde tablespace. Nu werkt het wel.

[Exocet]

mdcroon schreef op woensdag 27 juni 2007 @ 10:52:
Ja, tussentijds wordt inderdaad een commit uitgevoerd.

Commit tijdens DDL uitvoeren heeft geen zin.

Ik denk dat de melding komt omdat er nog een index bestaat met dezelfde naam, het lijkt erop dat de contraint wel gedropt is, maar de index niet. (bij het maken van een primary key wordt er altijd een index met dezelfde naam aangemaakt).
Wel vreemd, want bij een simpele test waar ik hetzelfde doe (ook 10g), wordt de primary key wel netjes gedropt en opnieuw aangemaakt.

In jouw situatie gaat het goed na het droppen van de tabel, omdat je hiermee ook de index hebt verwijderd.

[ Voor 8% gewijzigd door Exocet op 28-06-2007 08:55 ]

[mdcroon]

Exocet schreef op donderdag 28 juni 2007 @ 08:55:
[...]
Commit tijdens DDL uitvoeren heeft geen zin.

Ik weet dat een commit geen zin heeft in deze situaties maar op een gegeven moment begin je aan jezelf te twijfelen. Volgens mij gaat het fout omdat de tabel en constraint in twee verschillende table spaces staan.

Na het droppen van de tabel heb ik deze opnieuw aangemaakt en gecontroleerd dat de constraint in dezelfde table space staat. Vervolgens lukt het wel om de constraint te droppen en opnieuw aan te maken.

[JaQ]

mdcroon schreef op donderdag 28 juni 2007 @ 11:01:
[...]


Ik weet dat een commit geen zin heeft in deze situaties maar op een gegeven moment begin je aan jezelf te twijfelen. Volgens mij gaat het fout omdat de tabel en constraint in twee verschillende table spaces staan.

Na het droppen van de tabel heb ik deze opnieuw aangemaakt en gecontroleerd dat de constraint in dezelfde table space staat. Vervolgens lukt het wel om de constraint te droppen en opnieuw aan te maken.

Constraints staan niet in tablespaces, een constraint is immers geen object maar een attribuut van een object (in dit geval een tabel).

De bijbehorende unieke index die aangemaakt wordt staat wel in een tablespace. Als je nog geen unieke index op de primary key kolom hebt aangemaakt, zal Oracle een indexnaam voor je bedenken (SYS$.....) Deze index wordt niet gedropped op het moment dat je de constraint verwijderd. Echter indexes zitten in een andere namespace en je mag dezelfde naam voor een index als voor een constraint gebruiken.

Ik denk dus dat je ergens anders iets gemist hebt, de door jou beschreven werkwijze is volledig valide.