Detailedexplanation
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.
Classification
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,referentialintegritymaintainsthedefinedrelationshipsbetweentablestoensurethatthekeyvaluesareconsistentinalltables.Suchconsistencyrequirementsensurethatnon-existentvalueswillnotbereferenced.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.
Integrityconstraints
Integrityconstraintsmainlyincludeentityintegrityconstraints,referentialintegrityconstraints,functionaldependencyconstraints,andstatisticalconstraints.
EntityintegrityEntityintegritymeansthatallprimaryattributes(thatis,attributesoftheprimarycode)inarelationshipcannottakenullvalues.Theso-called"nullvalue"is"don'tknow"or"meaningless"value.Iftheprimaryattributetakesanullvalue,itmeansanunidentifiableentity,whichcontradictsthereal-worldapplicationenvironment,sothisentitymustnotbeacompleteentity.
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,thereferenceAlltheoutercodevaluesintherelationshiparedeletedtogetherwiththetuplecorrespondingtothemaincodevalueofthetupletobedeletedinthereferencedrelationship.Ifthereferencerelationshipisalsothereferencedrelationshipofanotherrelationship,thiscascadingdeletionshouldbecascadeddeletionItshouldbecascadeddown.③Considerationwhenmodifyingthemaincodeinthereferencedrelationship.Whenencounteringthissituation,theprocedureissimilartodeletingthetupleofthereferencedrelationship,andcascademodificationisrequired.Whilemodifyingthemaincodevalueinthereferencedrelationship,thecorrespondingoutercodevalueinthereferencedrelationshipismodifiedinthesameway.
FunctionaldependencyconstraintsMostofthefunctionaldependencyconstraintsareimplicitintherelationalpatternstructure,especiallytherelationalpatternswithahigherdegreeofnormalization(suchas3NForBCNS)aremaintainedbythepatterntomaintainfunctionaldependency.Inpracticalapplications,inordernottoseparatetheinformationtoomuch,itisgenerallynotpossibletopursuestandardizationtoomuch.Inthisway,therecanbesomefunctionsbetweenthefieldsoftherelationshiptobeexplicitlyexpressed.
Systemconstraintreferstotheconstraintrelationshipbetweenacertainfieldvalueandthestatisticalvalueofmultipletuplesinonerelationship.Forexample,thesalaryofthemanagerofthedepartmentshallnotbehigherthan5timestheaveragesalaryoftheemployeesofthedepartment.Theaveragewagevalueofemployeesisastatisticallycalculatedvalue.Inmanyoccasions,statisticaldatacanoftenbemadepublic,butindividualdataisconfidential,butindividualdatavaluescanbeinferredfromstatisticaldata,socertainprecautionsmustbetakentopreventdataleakage.
Implementationofintegrityconstraints
Themethodsofimplementingintegrityconstraintsvarybycategory.Integrityconstraintscanbedividedintotwocategories: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,theattributevaluesofsalaryandlengthofservicearegenerallyonlyWillincrease,notdecrease.Dynamicconstraintsaregenerallyalsoexplicitlystated.Amongtheaboveconstraints,inherentconstraintsmustbeimplemented,implicitconstraintsarebasicallyimplementedorpartiallyimplementedinmostmodernDBMSs,andexplicitanddynamicconstraintsareonlyimplementedinindividualDBMSs.