In fact, this might become part of my standard arsenal of DBA interview questions: do you name your constraints? Or something like that. The few extra taps on the keyboard will be The bottom line is don’t be a lazy DBA/Programmer and let the system If the primary key is made of more than one attribute then those attributes are irreducible. Primary key is always chosen from the possible candidate keys. Attributes which are part of a primary key are known as Prime attributes. Dropping and recreating a primary key on a table can be a veryĬostly operation and since there has been no functional change, oneĭan Jones blog: Best Practice: Naming Constraints The data values for the primary key attribute should not be null. New build, attempt to remove the 'old' constraint and add the 'new' This means that even though there has been no functional change, anyĬhange scripts generated using comparison software will, upon every Will be different on different databases. The Primary Key and recreate it, because the Junk portion of its name Uses a default auto-generated name, PK_TableName_Junk (for example, Normally look something like this: DF_MyTable_M圜olu_3F3159ABĬleaning Up SQL Server Deployment Scripts in the DRI Objectsįor example, if a Primary Key is created but left unnamed, SQL Server This will cause SQL Compare to drop the Primary Key and recreate it, because the Junk portion of its name will be different on different databases. If you do not specify a name, SQL will generate a random name builtįrom a prefix which denotes the type of constraint, and partial tableĪnd column names followed by a random alphanumeric string. For example, if a Primary Key is created but left unnamed, SQL Server uses a default auto-generated name, PKTableNameJunk (for example, PKCustomerA4AE64082CE326F2). How To Drop An Unnamed Constraint in SQL Server Posting this same question on dba.SE.com and I got this response So far, I have been able to find some new information on constraints and I can say I have learnt more. Information about this question, for me, is hard to come by. Summary: It is best practice to always name constraints What does this error means? What is the implication? Especially, Name the foreign key to avoid checking the entire table and only check the new constraint. Repeated generating a new set of random constraint names It would normally look something like this:Įverytime the DBProject is rebuilt and deployed this process is Partial table and column names followed by a random alphanumeric If you do not specify a name, SQL will generate a random name built from a prefix which denotes the type of constraint, and Team2_ID int not null REFERENCES Team(TeamID), Team1_ID int not null REFERENCES Team(TeamID), TournamentID int not null REFERENCES Tournament(TournamentID), Name the foreign key to avoid checking the entire I received this error while deploying a Visual Studio SQL Server DBProjectĭBProj.dbschema(0,0): Warning SQL01271: The unnamed foreign keyīetween tables.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |