hente foreign keys fra database

[RulleRimfrost]

Vb2005 og msql server 2005

Når man skal laste data til en tabell med foreign key, finnes det noen tricks for å hente den sist lagrede ID i master-tabellen da (denne må jo lagres i child-tabell) ?

F eks i følgende database :

 

Artist(

A_ID, int auto key

Name

)

 

Record(

A_ID, Foreign key - Artist

R_ID, int auto key

Title

Year

)

 

Song(

R_ID, Foreign key - Record

S_ID, int auto key

Title

Track

Rate

)

 

Om det er 1000 CD'er i databasen, og jeg maskinelt skal legge til f eks 100 CD'er til fra en liste eller stream. Jeg må vel da på en måte hente neste ubrukte key for alle tabeller, og deretter holde en stram telling med disse i mine dataset.fill rutiner.

 

Men, hvordan henter jeg jeg neste ledige tabell-index inn til variabler som jeg kan legge inn i dataset, før jeg updater dette til databasen ?

Endret 18. oktober 2007 av RulleRimfrost

[HDSoftware]

Her er du oppe i et klassisk problem. En SQL server produserer ID'en i det record lagres og derfor er den ikek kjent for deg før neste gang du leser posten. Du kan løse dette på to måter. Den ene måten er server type avhengig, mens den adre er helt uavhengig av server type.

Hvis du bruker MS SQL så kan du bruke @@IDENTITY. Den sørger for å returnere siste posten en klient opprettet på serveren, slik at du dermed får tilbake disse automatiske verdiene.

Den andre metoden, som er 100% server uavhengig, er å håndtere dette selv ved å bruke GUID. La ID være av typen VarChar(50) og gjør slik:

Artist_A_ID = new GUID().ToString();

Dette gir en verdi som ser slik ut: E86D9682-6284-43a0-A33E-496D60F55F49 og er en randomisert kalkulert ID som er "garantert" å være unik. Den beregnes på bakgrunn av DateTime, maskinvare (tror jeg) og et randomisert tall. Odsa for at to maskiner prøver å lage en GUID på nøyaktig samme tusendels sekund er svert liten. ANdre fordeler med å bruke GUID er at du veldig enkelt kan lage programmer som kan kjøre på forskjellige steder for så og "merge" dataene sammen mot en sentral løsning

[j000rn]

Hvis du bruker MS SQL så kan du bruke @@IDENTITY. Den sørger for å returnere siste posten en klient opprettet på serveren, slik at du dermed får tilbake disse automatiske verdiene.

 

Har du ikke hørt at "@@IDENTITY makes baby Jesus cry"?

SCOPE_IDENTITY() skal brukes isteden.

 

@@IDENTITY gjelder for alle sesjoner på hele SQL serveren, og i verste fall kan du få tilbake identity verdien fra en insert som kjørers "samtidig" av en annen bruker.

[HDSoftware]

Sier du det?!?! hmm, pussig. Var ganske sikker på at jeg leste i SQL dokumentasjonen for lenge siden at det ikke var slik. Kan dette ha vert endret siden SQL v7, eller var det v5?!? Husker ikke. Anyway, Scope_Identity() høres riktig ut uansett

[j000rn]

Sier du det?!?! hmm, pussig. Var ganske sikker på at jeg leste i SQL dokumentasjonen for lenge siden at det ikke var slik. Kan dette ha vert endret siden SQL v7, eller var det v5?!? Husker ikke. Anyway, Scope_Identity() høres riktig ut uansett

 

SCOPE_IDENTITY() kom vel i 2000 tror jeg...

[HDSoftware]

ok. Uansett er ikek dette måten jeg ville gjort det på, som tidligere nevnt. En GUID er like greit. Man kan vel strengt tatt også lage en Stored Procedure som gjør jobben med å opprette record, men da får man fort problemstillinger som opprydding hver gang man avbryter en INSERT. Nei, GUID er tingen spør du meg...

Endret 18. oktober 2007 av HDSoftware

[kaffenils]

@@IDENTITY gjelder for alle sesjoner på hele SQL serveren, og i verste fall kan du få tilbake identity verdien fra en insert som kjørers "samtidig" av en annen bruker.

 

Dette er faktisk en vanlig misforståelse. @@IDENTITY returnerer siste brukte identityverdi for din batch, og vil aldri påvirkes av hva andres sessions gjør. Problemet med at den gir deg siste verdi fra batchen er at evt. triggere som aktiveres også vil påvirke @@IDENETITY da disse er en del av batchjobben. SCOPE_IDENTITY() returnerer siste brukte identityverdi for din sesjons scope. Dvs de tabellene du eksplisitt har INSERTet til. Endringer triggere gjør er ikke med i scopet og vil dermed ikke påvirke SCOPE_IDENTITY().

 

Ingenting galt i å bruke @@IDENTITY bare du er 100% klar over hvordan den fungerer.

Det skal sies at selv bruker jeg KUN SCOPE_IDENTITY().

 

men da får man fort problemstillinger som opprydding hver gang man avbryter en INSERT. Nei, GUID er tingen spør du meg...

 

Hvilken opprydding er det du sikter til?

Du må gjerne bruke uniqueidentifier, men indexer blir jo litt større. Ikke det at jeg tror du merker noen ytelsesforskjell med mindre antall rader passerer mange titalls millioner. I tillegg bør en være klar over hvilken negativ effekt uniqueidentifier har hvis den er brukt i en clustered index. SQL Server 2005 har heldigivs løst dette med NEWSEQUENTIALID() funksjonen som kan brukes som default i tabeller.

[HDSoftware]

Jeg tenkte egentlig ikke på UniqueIdentifier på SQL siden, men en helt vanlig VARCHAR som tilordnes fra programmet med GUID.TOSTRING(). Ved å bruke denne metoden vil man aldri støte borti relasjonsproblemer uansett hvilken servertype man koder mot. Gjøres så enkelt som dette:

 

Faktura.FakturaID = new.guid().tostring();

 

og så hver gang man legger til en fakturalinje:

Fakturalinje.FakturaID = Faktura.FakturaID;

 

Vips så slipper man å tenke på noe som helst. Relasjonen ligger selvsagt på server siden slik at alle fakturalinjer automatisk kobles på rett fakturahode. Har egentlig aldri helt skjønt hensikten med den autoteller saken på SQL serveren. Ubruklig spør du meg. Jeg klarer ikke se ett eneste sted hvor jeg har behov for en record ID som er stigende eller synkende og numerisk. Mange vil sikkert argumentere med fakturanummer som autoinc på serveren, men jeg er ikke enig. Slike nummere vil man uansett ha i et eget mummer register slik at de kan håndteres fra programmets side.

[RulleRimfrost]

Ferdig på jobb, og svar allerede. Takker.

Jeg tenkte egentlig guid måtte får veldig store ytelseskonsekvenser, siden den må indexere varchar(50), men mulig det er veien å gå om jeg ikke får til dette. Serveren er en ny quad Xenon med sas-raid og 32Gb ram, så det blir vel neppe målbart...

 

Jeg ser på SCOPE_IDENTITY, og har laget en ny query i datasettet mitt:

INSERT INTO [Artist] ([Name]) VALUES (@Name);
SELECT SCOPE_IDENTITY()

 

Når jeg tester med:

Dim CollAdapter As New CollectionDataSetTableAdapters.ArtistTableAdapter()
Dim new_id As Integer = _
Convert.ToInt32(CollAdapter.InsertArtist("Prince"))
Label1.Text = new_id + 1

 

Så henter den ut new_id for neste rad, men er det mulig å hente den ut uten en INSERT først ?

Jeg kune tenkt meg å hente ut ID for alle tabellene, fylle dataset, og batch-oppdatere med BulkCopy. Har hørt at det er mye raskere...

 

Edit: jeg får feilmelding om jeg ikke har en insert i queryen før SELECT SCOPE_IDENTITY(), var det jeg skulle si...

Endret 18. oktober 2007 av RulleRimfrost

[kaffenils]

Har egentlig aldri helt skjønt hensikten med den autoteller saken på SQL serveren. Ubruklig spør du meg. Jeg klarer ikke se ett eneste sted hvor jeg har behov for en record ID som er stigende eller synkende og numerisk.

 

Er da mye bedre å bruke en 4 bytes integer enn en 16 byte guid som primary key hvis en ikke har noen fornuftig naturlig nøkkel. Dessuten vil en guid i en clustered index skape mengder av page splits, som er veldig ressurskrevende, i databasen. Skal du først bruke guid så la i alle fall SQL Server tilordne den med NEWSEQUENTIALID() da denne er stigende pr. tabell og dermed ikke skaper problemer som page splits.

[kaffenils]

Så henter den ut new_id for neste rad, men er det mulig å hente den ut uten en INSERT først ?

 

scope_identity henter ikke ut id for neste rad, den henter id for siste insert statement du utførte i batchen.

scope_identity uten INSERT er selvfølgelig ikke mulig.

[RulleRimfrost]

Heh. Er nok for vant med kompliserte løsninger for de enkleste ting (er Excel-statistiker)

Tror jeg løser dette med å kalle en stored procedure med return på SELECT MAX(ID) + 1 AS new_id FROM minTabell (om jeg får til).

Så legger jeg dette inn som variabler i vb-prosedyren som øker med +1 for hver AddRow til dataset. Så dumper jeg hele dataset til databasen. 4-5 av tabellene får en key som skal benyttes i ca 15 child-tabeller der jeg kan jo gjennbruke ID-variabelen. I child-tabellene lar jeg sql-server sette ID. Voila.

Det er vel strengt tatt ikke nødvendig at sql må legge inn ID via autoincrement. Som indexer og key's blir de vel like effektive uansett.

 

Trodde Identity var database-spesifkk metadata, men skjønner nå den er transaksjons-spesifikk.

[kaffenils]

Heh. Er nok for vant med kompliserte løsninger for de enkleste ting (er Excel-statistiker)

Tror jeg løser dette med å kalle en stored procedure med return på SELECT MAX(ID) + 1 AS new_id FROM minTabell (om jeg får til).

Så legger jeg dette inn som variabler i vb-prosedyren som øker med +1 for hver AddRow til dataset. Så dumper jeg hele dataset til databasen. 4-5 av tabellene får en key som skal benyttes i ca 15 child-tabeller der jeg kan jo gjennbruke ID-variabelen. I child-tabellene lar jeg sql-server sette ID. Voila.

Det er vel strengt tatt ikke nødvendig at sql må legge inn ID via autoincrement. Som indexer og key's blir de vel like effektive uansett.

 

 

Og hvordan skal du håndtere at andre brukere inserter data mellom det tidspunktet du har hentet max+1 og til du faktisk kjører INSERT-statementet. Hvordan håndtere du at ikke andre kjører max+1 i mellomtiden?

Skal du xlocke hele tabellen slik at andre ikke får lest max+1? Skal du kun tillate inserts gjennom den ene prosedyren og bruke sp_getapplock (eller hva den nå heter)?

Sier ikke dette for å ta motet fra deg. Vil bare gjøre deg oppmerksom på hvilke problemestillinger du må ta hensyn til når du lager din egen autonumereringsrutine.