Целостта на данните

Подробно обяснение

Thedatainthedatabaseisinputfromtheoutside,andtheinputofthedataduetovariousreasons,invalidinputorwronginformationwilloccur.Ensuringthattheinputdatameetstheregulationshasbecometheprimaryconcernofdatabasesystems,especiallymulti-userrelationaldatabasesystems.Dataintegrityisthereforeraised.ThischapterwilldescribetheconceptofdataintegrityanditsimplementationinSQLServer.

DataIntegrityreferstotheaccuracyandreliabilityofdata.Itisproposedtopreventtheexistenceofdatathatdoesnotmeetthesemanticrequirementsinthedatabaseandpreventinvalidoperationsorwronginformationcausedbytheinputandoutputofwronginformation.Dataintegrityisdividedintofourcategories:EntityIntegrity,DomainIntegrity,ReferentialIntegrity,andUser-definedIntegrity.

Databaseusesavarietyofmethodstoensuredataintegrity,includingforeignkeys,constraints,rules,andtriggers.Thesystemhandlestherelationshipbetweenthesefourwell,andusesdifferentmethodsfordifferentspecificsituations,andcross-useseachothertomakeupfortheshortcomings.

Класификация

Dataintegrityisdividedintothefollowingthreecategories.

(1)Domainintegrity:referstotheinputvalidityofacolumn,whetheritisallowedtobenull.Themethodstoenforcedomainintegrityare:restrictiontype(bysettingthedatatypeofthecolumn),format(throughCHECKconstraintsandrules)ortherangeofpossiblevalues​​(throughFOREIGNKEYconstraints,CHECKconstraints,DEFAULTdefinitions,NOTNULLdefinitionsandrules).Forexample,thestudent'stestscoremustbebetween0and100,andthegendercanonlybe"male"or"female".

(2)Entityintegrity:referstoensuringthatallrowsinthetableareunique.Entityintegrityrequiresthatallrowsinthetablehaveauniqueidentifier.Thisuniqueidentifiermaybeacolumnoracombinationofseveralcolumns,calledtheprimarykey.Inotherwords,theprimarykeyinthetablemusttakeauniquevalueonallrows.Methodstoenforceentityintegrityare:index,UNIQUEconstraint,PRIMARYKEYconstraintorIDENTITYattribute.Forexample,thevalueofsno(studentnumber)inthestudenttablemustbeunique,ituniquelyidentifiesthestudentrepresentedbythecorrespondingrecord,andrepeatedstudentnumbersareillegal.Thestudent'snamecannotbeusedastheprimarykey,becauseitisentirelypossiblethattwostudentshavethesamenameandsurname.

(3)Referentialintegrity:referstoensuringthereferentialrelationshipbetweentheprimarykey(referencedtable)andexternalkey(referencedtable).Itinvolvesmaintainingtheconsistencyofdataintwoormoretables.Theforeignkeyvalueassociatestherecordsinthereferencetablethatcontaintheforeignkeywiththerecordsinthereferencedtablewhoseprimarykeymatchestheforeignkey.Whenentering,changing,ordeletingrecords,referentialintegritymaintainsthedefinedrelationshipsbetweentablestoensurethatthekeyvalues​​areconsistentinalltables.Suchconsistencyrequirementsensurethatnon-existentvalues​​willnotbereferenced.Ifthekeyvalueischanged,thenallreferencestothekeyvaluemustbeconsistentlychangedintheentiredatabase.Referentialintegrityisbasedontherelationshipbetweenforeignkeysandprimarykeys.Forexample,thecoursenumberofastudent'sstudycoursemustbeavalidcoursenumber.Theforeignkeycno(coursenumber)ofthescoretable(scoretable)willrefertotheprimarykeycno(coursenumber)inthecoursetable(coursetable)toachievedataintegrity.

Domainintegrity,entityintegrity,andreferentialintegrityareimplementedoncolumns,rows,andtablesrespectively.Dataintegritycanbeimplementedatanytime,butwhenimplementingdataintegrityonatablewithexistingdata,thesystemmustfirstcheckwhetherthedatainthetablemeetstheimplementedintegrity,andonlythedatainthetablemeetstheimplementedintegrity,Dataintegritycanbeimplementedsuccessfully.

Ограничения на целостта

Ограничения на целосттаmainlyincludeentityintegrityconstraints,referentialintegrityconstraints,functionaldependencyconstraints,andstatisticalconstraints.

Интегритет на обектаИнтегритет на обекта означава, че всички първични атрибути (това, атрибути на първичния код) във връзката не могат да приемат нулеви стойности. Така наречената "нулева стойност" е "не знам" или "безсмислена" стойност. Ако първичният атрибут приема нулева стойност, това означава неидентифицируем обект, което противоречи на средата на приложението от реалния свят, така че не трябва да се допълва от този обект.

ReferentialintegrityconstraintReferentialintegrityconstraintreferstothevalueoftheoutercodeinthereferencerelationshiporthenullvalue(eachattributeoftheoutercodeisanullvalue)oracertainelementinthereferencedrelationshipThemaincodevalueofthegroup.Thefollowingisanexample.Therearetworelationshipmodels:student(studentnumber,name,gender,professionalnumber,age)andmajor(professionalnumber,professionalname).Payattentiontothefollowingissueswhenimplementingreferentialintegrity:①Whethertheoutercodecanacceptnullvalues.Becausewhethertheoutercodecanbeemptyisdependentontheapplicationenvironment,iftherearetworelationalmodesforelective(studentnumber,coursegood,grade)students(studentnumber,name,gender,age,department),theelectiverelationshipisinThe“studentID”oftheexternalcodecannotbeempty.Ifitisempty,itmeansthatastudentwhodoesnotknowthestudentIDhastakenacertaincourse,whichisinconsistentwiththeschool’sapplicationenvironment.②Considerationwhendeletingtheoriginalgroupofthereferencedrelationship.Sometimesitisnecessarytodeleteacertainoriginalgroupofthereferencedrelationship,andtheoutercodevalueofseveraloriginalgroupsinthereferencedrelationshipcorrespondstothemaincodevalueofthedeletedreferencedrelationship.Atthistime,cascadedeletionisrequired,thatis,thereferenceAlltheoutercodevalues​​intherelationshiparedeletedtogetherwiththetuplecorrespondingtothemaincodevalueofthetupletobedeletedinthereferencedrelationship.Ifthereferencerelationshipisalsothereferencedrelationshipofanotherrelationship,thiscascadingdeletionshouldbecascadeddeletionItshouldbecascadeddown.③Considerationwhenmodifyingthemaincodeinthereferencedrelationship.Whenencounteringthissituation,theprocedureissimilartodeletingthetupleofthereferencedrelationship,andcascademodificationisrequired.Whilemodifyingthemaincodevalueinthereferencedrelationship,thecorrespondingoutercodevalueinthereferencedrelationshipismodifiedinthesameway.

FunctionaldependencyconstraintsMostofthefunctionaldependencyconstraintsareimplicitintherelationalpatternstructure,especiallytherelationalpatternswithahigherdegreeofnormalization(suchas3NForBCNS)aremaintainedbythepatterntomaintainfunctionaldependency.Inpracticalapplications,inordernottoseparatetheinformationtoomuch,itisgenerallynotpossibletopursuestandardizationtoomuch.Inthisway,therecanbesomefunctionsbetweenthefieldsoftherelationshiptobeexplicitlyexpressed.

Systemconstraintreferstotheconstraintrelationshipbetweenacertainfieldvalueandthestatisticalvalueofmultipletuplesinonerelationship.Forexample,thesalaryofthemanagerofthedepartmentshallnotbehigherthan5timestheaveragesalaryoftheemployeesofthedepartment.Theaveragewagevalueofemployeesisastatisticallycalculatedvalue.Inmanyoccasions,statisticaldatacanoftenbemadepublic,butindividualdataisconfidential,butindividualdatavalues​​canbeinferredfromstatisticaldata,socertainprecautionsmustbetakentopreventdataleakage.

Внедряване на ограничения за интегритет

Themethodsofimplementingintegrityconstraintsvarybycategory.Ограничения на целосттаcanbedividedintotwocategories:staticconstraintsanddynamicconstraints.

StaticconstraintsStaticconstraintsareconstraintsonthestateofthedatabase,whichcanbedividedintofixedconstraints,implicitconstraintsanddisplayconstraints.

Fixedconstraintsareinherentconstraintsofthedatamodel.Forexample,theattributesoftherelationshipareatomic,thatis,theconstraintsofINFaresatisfied.TheinherentconstraintshavebeenconsideredintheimplementationoftheDBMS.

Implicitconstraintsrefertoconstraintsimplicitinthedatamodel,whicharegenerallydescribedindatadefinitionlanguage(DDL)statementsandstoredinthedatadirectory.Forexample,domainintegrityconstraints,entityintegrity,andreferentialintegrityconstraintsarealldescribedbycorrespondingDDLstatements.

DisplayconstraintsInherentconstraintsandimplicitconstraintsarethemostbasicconstraints,buttheycan’tsummarizeallconstraints.Dataintegrityisdiverseanddependsonthesemanticsandapplicationsofthedata.Theseconstraintsareonlyexplicitlystated,sotheyarecalledexplicitconstraints.Therearegenerallythreewaystoexplainexplicitconstraints:①Useprocedurestoexplainconstraints.Thismethoddoesnotconstrainthedescriptionandverificationtotheapplicationprogram,andsomeprocedurescanbeinsertedintheapplicationprogramtocheckwhetherthedatabaseupdateviolatesthegivenconstraint,andiftheconstraintisviolated,thetransactionisrolledback.Theprocessofcheckingconstraintsisgenerallywritteninageneralhigh-levelprogramminglanguage,whichcanexpressvariousconstraints.Thisisauniversalmethod.②Explaintheconstraintsinlanguage.Assertionsrefertologicalconditionsthatthedatabasestatemustmeet.Databaseintegrityconstraintscanbeseenasacollectionofaseriesofassertions.Inordertoexpressconstraints,theDBMSmustprovideanassertionspecificationlanguage.③Usetriggerstoexpressconstraints.ThetriggerisasoftwaremechanismwhosefunctionisequivalenttoWHENEVERTHEN,thatis,onceagivenconditionisestablished,thesystemtriggersacorrespondingaction.Thetriggercanbeusedtoexpresstheconstraint,withtheviolationoftheconstraintasthecondition,andtheviolationoftheconstraintastheaction.Theactionisnotlimitedtorollingbackthetransaction,itcanalsogivetheuseramessageorprocess.Afterabatchoftriggersaredefinedinthesystem,thedatabasestatuswillbemonitored.Onceanupdatethatviolatestheconstraintsoccurs,correspondingactionswillbetriggered.

DynamicconstraintsDynamicconstraintsarenotconstraintsonthestateofthedatabase,butconstraintsthatshouldbeobservedwhenthedatabasechangesfromonestatetoanother.Forexample,whenupdatingtheemployeetable,theattributevalues​​ofsalaryandlengthofservicearegenerallyonlyWillincrease,notdecrease.Dynamicconstraintsaregenerallyalsoexplicitlystated.Amongtheaboveconstraints,inherentconstraintsmustbeimplemented,implicitconstraintsarebasicallyimplementedorpartiallyimplementedinmostmodernDBMSs,andexplicitanddynamicconstraintsareonlyimplementedinindividualDBMSs.