The influence of normative SQL script on database operation and maintenance from a simple constraint

Source: A simple constraint on the effect of normative SQL scripts on database operation and maintenance

Before mentioning some of the characteristics of the constraints, it does not seem to be a big deal of problems, http://www.cnblogs.com/wy123/p/7350265.html
The following is an indication of the importance of the specification in the actual production operations.

The following is a simple script that does not appear to be a problem on the surface.
It creates 3 constraints, a PRIMARY KEY constraint, a unique constraint, a default value constraint, and the script executes without any problems.

 UseTestGOif exists(Select 1  fromSys.tableswhereName= 'Testconstraint')    Drop Tabledbo. TestconstraintGOCreate Tabledbo. Testconstraint (IDint Primary Key, namevarchar( -)Unique, CreateDate datedefault getdate())GO

The following is the constraint information generated after the execution of the above table script, and you can see that the resulting constraint is a constraint name generated by a certain rule + random character.
To tell you the truth, this is not the way we want it to be named, as mentioned earlier, we don't want to see a database with unintended or random naming information.
Of course, not only the cause of obsessive-compulsive disorder, but to see a canonical naming.

As the requirements change, the type of a field needs to be modified, and when the script that modifies the field type is executed, an error is found because there is a constraint on the field, and if you want to modify the field type, delete the constraint first.

Since the field type cannot be modified directly, it is no problem to delete the constraint and then redefine the field type.

　　

But the problem here is that the execution of the change script must be modified from the development environment, then tested, then on the test environment, tested, and finally executed on the production environment.
There is no guarantee that scripts written in the development environment can be executed directly in the test environment and can be performed in the production environment.
The entire process is basically automated execution, scripts are also universal execution, if the middle change to change, is not to waste a lot of irrelevant time.
Development environment Write one, test environment write one, production environment write one? And need a one to open with ssms or to see the name of a constraint on a field in a specific environment through a system table?
In some cases, it is not easy to access some sensitive production databases.
Of course, some people say I can connect to the production library at any time, can be used at any time through the SSMs graphical interface operation, this case exception is not discussed.
This situation is obviously not very normative, and is to increase the amount of unnecessary workload, I think valuable work is not to do these meaningless repetitive labor.

To circumvent such situations, the name of the specification will be executed from the start of the table, and the resulting constraint will be the specified name, regardless of the environment in which the table is built.
In the case of modifying the field type above, the written script can be generalized in each environment.

 UseTestGOif exists(Select 1  fromSys.tableswhereName= 'Testconstraint')    Drop Tabledbo. TestconstraintGO--do not specify constraints when building a table, which generates a random constraint nameCreate Tabledbo. Testconstraint (IDintNot null, namevarchar( -), CreateDate date)GO--PRIMARY KEY constraintAlter Tabledbo. TestconstraintAdd constraint [Pk_testconstraint] Primary Key  Clustered(ID)GO--UNIQUE ConstraintAlter Tabledbo. TestconstraintAdd constraint [Uq_testconstraint_name] Unique(name)GO--Default value constraintAlter Tabledbo. TestconstraintAdd constraint [df_testconstraint_createdate] DEFAULT GETDATE() forCreateDateGO

Of course, in modifying the field type of the script in order to rigorous period, but also to do can be repeated, the following is only an example, in short, to be as normative and rigorous as possible to reduce the hassle of no matter.

if exists(Select 1  fromSys.default_constraintswhereName= 'df_testconstraint_createdate')begin    Alter Tabledbo. TestconstraintDrop constraintdf_testconstraint_createdateEndGoAlter TableTestconstraintAlter columnCreateDatedatetimeGOAlter Tabledbo. TestconstraintAdd constraintDf_testconstraint_createdateDEFAULT GETDATE() forCreateDateGO

Database from the installation, to external development and use, to the later operations, and even to retire, there are a series of normative operations.
This article only from the time constraint this very small aspect, to explain the normative influence to the development and the operation and maintenance work.
A house does not sweep the world, standard no small matter, the database is no exception,
Speaking of norms, many people disdain, think dispensable, such as broken lip three paradigms,
Some people take "moderate redundancy" to escape the three paradigms, think "moderate redundancy" is a fashionable + fashion + awesome + despise the rigid design,
For relational databases, violating the three-paradigm "moderate redundancy" once considered insufficiently comprehensive, encountered data inconsistency, even if you die, you go to repair the data.

The above "change field" a sentence looks simple, encountered this kind of thing, behind a series of fucked-up operations, if there are often similar problems, the value of the work where?
From the most basic norms can be seen to a team's work style and technical ability.

The influence of normative SQL script on database operation and maintenance from a simple constraint