[SQL,interbase]primary key veranderen (dependencies)

[SilentStorm]

achtergrond:
Ik probeer de agendafunctie van een soort boekhoudprogramma te 'maken' (als in 'repareren'). Hoewel de oorspronkelijke maker wel rekening hield met meerdere gebruikers, is die ergens vergeten om een primary key functie op die gebruiker te zetten.

Kortom, op dit moment is een agenda-item uniek op de datum en de tijd, maar dit moet veranderen naar de datum, tijd EN gebruiker.

gevonden oplossingen
Na een stuk aanprutsen met proberen de pk te veranderen en het not null maken van de betreffende kolom mbv deze procedure, vond ik deze faq.
Hieruit blijkt dat het niet mogelijk is de primary key te veranderen zonder de tabel opnieuw aan te maken. Dezelfde informatie vind ik in [rml][ SQL] Primary Key wijzigen[/rml] weer terug. Onderaan dat topic geeft ACM al aan hoe het moet:

create table new_tabel (...);
insert into new_tabel (velden....) select veldnamen from tabel;
drop table tabel;
alter table new_tabel rename to tabel;

uiteindelijk probleem:
Er blijken 4 dependencies op deze (agenda) tabel te staan (waarvan 3 op die gebruiker kolom waar het om gaat). Met handmatig zoeken heb ik een aantal procedures gevonden die hierop betrekking hebben.

Kan ik zonder die procedures te verwijderen oid die dependencies omzeilen?

[EfBe]

Je kunt met ALTER TABLE een nieuwe PK constraint aanmaken. Zie de LANGREF pdf van Interbase.

Een PK is gewoon een constraint. Je kunt ook eerst de PK constraint verwijderen en daarna een nieuwe aanmaken.

[ Voor 39% gewijzigd door EfBe op 16-04-2004 22:22 ]

[Soultaker]

Dit soort constraints gelden vaak tussen consistente snapshots van de database. Ik heb verder geen kennis van interbase, dus het is een gokje, maar ik kan me voorstellen dat het goed gaat als je de queries die je zelf al aangaf in een transactie stopt. In theorie zou dan zowel voor als na de operatie aan alle constraints voldaan moeten zijn (aangenomen dat je nieuwe tabel aan alle benodigde voorwaarden voldoet).

[SilentStorm]

EfBe schreef op 16 april 2004 @ 22:21:
Je kunt met ALTER TABLE een nieuwe PK constraint aanmaken. Zie de LANGREF pdf van Interbase.

Een PK is gewoon een constraint. Je kunt ook eerst de PK constraint verwijderen en daarna een nieuwe aanmaken.

bij het verwijderen krijg ik een 'index in use'. Omdat de hierboven genoemde faq al aangaf dat het niet kon zonder de tabel opnieuw aan te maken, heb ik er verder geen aandacht aan besteed.

Soultaker schreef op 16 april 2004 @ 22:23:
Dit soort constraints gelden vaak tussen consistente snapshots van de database. Ik heb verder geen kennis van interbase, dus het is een gokje, maar ik kan me voorstellen dat het goed gaat als je de queries die je zelf al aangaf in een transactie stopt. In theorie zou dan zowel voor als na de operatie aan alle constraints voldaan moeten zijn (aangenomen dat je nieuwe tabel aan alle benodigde voorwaarden voldoet).

Goed punt! De maker heeft steeds de (levensgevaarlijke!) autocommit gebruikt, maar voor het aanpassen kan die natuurlijk zo uit. De applicatie is echter dusdanig groot dat het aanpassen met commits ed te ver gaat. Anyway -- * SilentStorm gaat het eens proberen zo.

edit: bij het wijzigen van de sqlpasstroughmode krijg ik een weigering: 'can not change sqlpasstroughmode'. Als ik het vervolgens wil proberen in een keer uit te voeren kom ik het volgende probleem tegen: het renamen kan niet in SQL mode. De dbms geeft me wel de mogelijkheid om tables te renamen met een (niet SQL) rename functie, maar dat lukt weer niet in combi met de autocommit.

Als ik een nieuwe primary key aan wil maken loop ik weer tegen het dependency probleem op: de column waar het om gaat is niet NOT NULL en om hem zo te maken, moet ik een nieuw column aanmaken, daar de data instoppen (geen probleem) en vervolgens de oude column verwijderen (wel een probleem -- dependencies), en de nieuwe naar de oude kopieeren).

/me gaat nog even verder prutsen

[ Voor 26% gewijzigd door SilentStorm op 16-04-2004 22:41 ]

[Soultaker]

@EfBe: als je alle bestaande afhankelijke objecten opnieuw wil aanmaken moet je ze wel eerst vinden. Behalve foreign keys van andere tabellen kunnen ook dingen als user-defined functies en stored procedures gebruiken maken van die tabel. De vraag is hoe flexibel interbase daar mee is.

Overigens zou ik in dit geval zelf alle data dumpen naar een SQL script (bijna alle databases ondersteunen dat), de tabeldefinitie aanpassen in de SQL code en dan de database droppen en opnieuw vullen met het bewerkte script. Dat vereist wel dat de inhoud van de tabellen hetzelfde is gebleven (je hebt dus alleen indices en een paar simpele types enzo aangepast) anders kloppen de INSERT queries in je script niet meer.

[SilentStorm]

Soultaker schreef op 16 april 2004 @ 22:30:
Overigens zou ik in dit geval zelf alle data dumpen naar een SQL script (bijna alle databases ondersteunen dat), de tabeldefinitie aanpassen in de SQL code en dan de database droppen en opnieuw vullen met het bewerkte script. Dat vereist wel dat de inhoud van de tabellen hetzelfde is gebleven (je hebt dus alleen indices en een paar simpele types enzo aangepast) anders kloppen de INSERT queries in je script niet meer.

Lijkt me een goed idee. Ik heb eerder al nagelopen dat alle data gewoon kan worden ingevuld in een NOT NULL veld (er zijn geen null-waardes). Ik houdt jullie op de hoogte.

edit: ik heb wat zitten rondzoeken naar backup functies. Er lijkt iets te bestaan door het eerst te importeren in een 'dictionary' en dat te backuppen. importeren geeft echter de melding dat een veld niet kon worden ingevoerd zonder een referentie nar de lokatie daarvan. Een andere backupfunctie (in het 'interbase windows ISQL' programma geeft mij een (binaire) backup die ik niet zo kan aanpassen (metadata file, .ddl). Andere functies die en de datadefinities en de inhoud exporteren zie ik nog niet. (behalve het exporteren van je huidige sessie). *pluist nu de help uit.

edit2: misschien is het nog handig om nog te vermelden dat het om interbase versie 5 gaat. Niet heel oud, maar oplossingen met nieuwere functies gaan in dat geval niet lukken. Ik verwacht nog wel dat die sql script export functie ergens te vinden is. Je kunt sowieso je huidige sessie exporteren.

[ Voor 43% gewijzigd door SilentStorm op 16-04-2004 23:22 ]

[Delphi32]

Anders pak je toch gewoon een Datapump? Bij de BDE zit er eentje (als je BDE nog hebt tenminste) en er zwerven wel wat aardige implementaties rond op het web. Genereer daarmee een create-script (ddl dus) van je db, pas die aan, genereer nieuwe db met dat script en handel de rest af met een datapump. Easy

[EfBe]

Soultaker schreef op 16 april 2004 @ 22:30:
@EfBe: als je alle bestaande afhankelijke objecten opnieuw wil aanmaken moet je ze wel eerst vinden. Behalve foreign keys van andere tabellen kunnen ook dingen als user-defined functies en stored procedures gebruiken maken van die tabel. De vraag is hoe flexibel interbase daar mee is.

Daarom zei ik ook dat je alter table moet gebruiken Het lijkt me eerlijk gezegd sterk dat je een PK niet kunt wijzigen op een bestaande table.

[SilentStorm]

Ik ben heb er gisteravond mee moeten ophouden wegens de tijd mijn laatste poging behelsde het verwijderen van elke betrokken procedure (ik heb het script daarvan geexporteerd). Ik post waarschijnlijk maandag hoe het is opgelost

@EfBe: het wijzigen op zich is dan ook niet het probleem (of het wil weet ik nog niet want...). Wat dat wel is, is het feit dat de column die ik ervoor wil gebruiken geen NOT NULL attribuut heeft, wat hem ongeschikt maakt om direct als pk te fungeren. Bij het omzetten hiervan komen de dependencies weer om de hoek kijken.

[ Voor 4% gewijzigd door SilentStorm op 17-04-2004 15:19 ]

[EfBe]

Mja, als je een column wilt gebruiken die nu NULL's bevat, is het wellicht verstandig eens dieper na te denken over de waarde van de gegevens die je nu in je db hebt.

[SilentStorm]

EfBe schreef op 17 april 2004 @ 19:39:
Mja, als je een column wilt gebruiken die nu NULL's bevat, is het wellicht verstandig eens dieper na te denken over de waarde van de gegevens die je nu in je db hebt.

Hehe, het is niet eens mogelijk een primary key te maken over een waarde die niet NOT NULL is. Het gaat juist om het feit deze column om te zetten naar eentje die als NOT NULL is gedefinieerd. (zie ook hierboven )

[EfBe]

SilentStorm schreef op 17 april 2004 @ 23:18:
Hehe, het is niet eens mogelijk een primary key te maken over een waarde die niet NOT NULL is.

Ja dat hoef je mij niet te vertellen.

Het gaat juist om het feit deze column om te zetten naar eentje die als NOT NULL is gedefinieerd. (zie ook hierboven )

Oh? Ik kreeg de indruk dat je een extra veld aan de PK wilde toevoegen, nl. 'gerbuiker' ?

En met ALTER TABLE foo ALTER COLUMN... werkt dat niet? Het zou me echt verbazen als dat NIET zou werken. (wel eerst alle NULL's omzetten in een fatsoenlijke waarde natuurlijk, en daarbij moet je nadenken over welke waarde je dan gebruikt, of die een semantische waarden hebben.)

[ Voor 14% gewijzigd door EfBe op 18-04-2004 11:09 ]

[SilentStorm]

EfBe schreef op 18 april 2004 @ 11:04:
[...]

Ja dat hoef je mij niet te vertellen.


[...]

Oh? Ik kreeg de indruk dat je een extra veld aan de PK wilde toevoegen, nl. 'gerbuiker' ?

En met ALTER TABLE foo ALTER COLUMN... werkt dat niet? Het zou me echt verbazen als dat NIET zou werken. (wel eerst alle NULL's omzetten in een fatsoenlijke waarde natuurlijk, en daarbij moet je nadenken over welke waarde je dan gebruikt, of die een semantische waarden hebben.)

We praten een beetje langs elkaar heen: wat er moet gebeuren is inderdaad het genereren van een primary key over de drie genoemde velden, waarbij 'gebruiker' al wel aanwezig was, maar geen NOT NULL had staan. Het veranderen daarvan kan alleen door de column opnieuw aan te maken en te hernoemen. Bij het verwijderen van de oude versie ontstaat vervolgens een probleem omdat er nog dependencies bestaan.

[EfBe]

SilentStorm schreef op 18 april 2004 @ 16:42:
We praten een beetje langs elkaar heen: wat er moet gebeuren is inderdaad het genereren van een primary key over de drie genoemde velden, waarbij 'gebruiker' al wel aanwezig was, maar geen NOT NULL had staan. Het veranderen daarvan kan alleen door de column opnieuw aan te maken en te hernoemen. Bij het verwijderen van de oude versie ontstaat vervolgens een probleem omdat er nog dependencies bestaan.

Leg me dan eens uit waarom in vredesnaam je de column moet verwijderen? NOT NULL is een element van de column definitie, die kun je toch wijzigen met ALTER COLUMN in een ALTER TABLE? Of vergis ik me? (kan hoor ). Dus EERST alle NULL's verwijderen (dus waarde geven), dan de ALTER TABLE bla ALTER COLUMN... call, comitten die hap en dan moet je column voorzien zijn van de definitie NOT NULL. Maar dat kan niet in interbase?

[SilentStorm]

Laat ik beginnen met de mededeling dat het vandaag opgelost is door de betreffende procedures te verwijderen.

Een alter column (@ alter table) gaf mij geen mogelijkheden om een NOT NULL toe te voegen. ik lijk niet de enige te zijn en heb het op die manier geprobeert. Mogelijk komt dit ook omdat ie op het levensgevaarlijke (ervaring ) autocommit is vastgesteld. (Heb geen mogelijkheid het te wijzigen). zie ook [rml]SilentStorm in "[ SQL,interbase]primary key veranderen (d..."[/rml] hierboven.

Daarbij natuurlijk hartelijk dank voor de geboden oplossingen. Als je ooit eens iets WOS of NT'erigs hebt roep je maar

[EfBe]

Ik bedacht me net dat je wellicht ook in de RDB$FIELDS table het veld RDB$NULL_FLAG op 0 had kunnen zetten (geen idee of dat kan, het staat iig in die table ). Wel handig hoor, die pdf's van interbase.