从一个简单的约束看规范性的SQL脚本对数据库运维的影响

 

之前提到了约束的一些特点，看起来也没什么大不了的问题，http://www.cnblogs.com/wy123/p/7350265.html
以下以实际生产运维中遇到的一个问题来说明规范的重要性。

如下是一个简单的建表脚本，表面上看起来并没有什么问题。
其中创建了3个约束，一个主键约束，一个唯一约束，一个默认值约束，该脚本执行起来没有任何问题。

USE Test
GO

if exists(select 1 from sys.tables where name = 'TestConstraint')
    drop table dbo.TestConstraint
GO

create table dbo.TestConstraint
(
    id int primary key,
    name varchar(100) unique,
    createdate date default getdate()
)
GO

 

如下是上述建表脚本执行之后生成的约束信息，可以看到生成的约束都是按照一定规则+随机字符生成的约束名字。
实话说这不是我们想要的命名方式，之前提到过，我们不愿意看到数据库中存在非预期或者说是随机的命名信息.
当然不仅仅是强迫症的原因，非要看到一个规范的命名。

随着需求的变更，需要修改一个字段的类型，当执行修改字段类型的脚本的时候，发现报错了，原因是字段上有一个约束，如果想要修改字段类型，就要先删除这个约束。

 　　

既然无法直接修改字段类型，那么就删除该约束，再重定义字段类型，也是没有问题的。

　　

　　但是问题就出在这里，变更脚本的执行肯定是从开发环境开始修改，然后测试，然后再上测试环境，测试通过，最后才上生产环境执行。
　　这里没办法保证，在开发环境写完的脚本，可以直接在测试环境执行，可以在生产环境执行。
　　整个流程基本上是自动化执行的，脚本也是通用性执行的，如果中间改来改去，是不是要浪费很多无所谓的时间。
　　难不成开发环境写一个，测试环境写一个，生产环境写一个？并且需要一个一个用SSMS打开或者通过系统表来查看具体环境中字段上约束的名称？
　　某些情况下，对于某些敏感的生产数据库，不是轻易就可以访问的。
　　当然有人说老子可以随时连上生产库，随时可以通过SSMS图形界面进行操作，这种情况例外不讨论。
　   此种情况显然是不太符合规范的，并且是增加了无所谓的工作量，我想有价值的工作绝不是做这些毫无意义的重复性劳动吧。

 

 

要想规避此类情况，就要从建表开始，建表的过程中就执行规范的名字，然后这个建表脚本不管在哪个环境，生成的约束都是指定的名字。
在上述修改字段类型的情况下，写的脚本就可以通用在各个环境中了。

USE Test
GO

if exists(select 1 from sys.tables where name = 'TestConstraint')
    drop table dbo.TestConstraint
GO
--不要在建表的时候指定约束，这样会生成随机的约束名字
create table dbo.TestConstraint
(
    id int not null,
    name varchar(100) ,
    createdate date 
)
GO

--主键约束
alter table dbo.TestConstraint
    add constraint [PK_TestConstraint] primary key  clustered (id)  
GO

--唯一约束
alter table dbo.TestConstraint
    add constraint [UQ_TestConstraint_name] unique(name)  
GO

--默认值约束
alter table dbo.TestConstraint
add constraint [DF_TestConstraint_createdate] DEFAULT GETDATE() FOR createdate
GO

当然在修改字段类型的脚本为了严谨期间，也要做到可以重复执行，以下仅为示例，总之就是要尽可能规范性和严谨性，以减少无所谓的麻烦。

if exists(select 1 from sys.default_constraints where name = 'DF_TestConstraint_createdate')
begin
    alter table dbo.TestConstraint
    drop constraint DF_TestConstraint_createdate
end
go

alter table TestConstraint
alter column createdate datetime
GO

alter table dbo.TestConstraint
add constraint DF_TestConstraint_createdate DEFAULT GETDATE() FOR createdate
GO

 

数据库从安装，到对外开发使用，到后期的运维，甚至到退役，有一系列的规范性操作。
本文仅从建表时候约束这个个非常小的方面，来说明规范性对开发以及运维工作的影响。
一屋不扫可以扫天下，规范无小事，数据库也不例外，
说到规范，很多人不屑，认为可有可无，比如说破嘴皮子的三范式，
有人拿着“适度冗余”来逃避三范式，觉得“适度冗余”是一个时髦+时尚+牛逼+鄙视呆板的一种设计，
对于关系数据库，违反三范式的“适度冗余”一旦考虑的不够周全，遇到数据的不一致性，就算你死了，你自己去修复数据吧。

以上“改字段”一句话看起来简单，遇到这种事，背后一系列操蛋性的操作，如果经常有类似的问题，工作的价值又在哪里呢？
从最基本的规范就可以看出来一个团队的工作风格和技术能力。

 

 

 

20170911补充，后面想了一下，还是可以稍微智能一点，脱离具体的约束的名字的情况下，自动删除约束+重定义字段+创建同名约束

采用动态SQL，参考如下

create table dbo.TestConstraintsName
(
    id int,
    createdate date
)
GO

--默认值约束
alter table dbo.TestConstraintsName
add constraint [DF_TestConstraintsName_createdate] DEFAULT GETDATE() FOR createdate
GO

/*
    1，判断createdate字段上是否存在约束
    2，如果存在约束，删除这个约束（如果没有约束，直接重定义字段类型）
    3，重定义字段类型
    4，用原始的约束名字重新重新定义这个约束
*/
declare @default_constraints_name varchar(200)
select @default_constraints_name = a.name
from sys.default_constraints a 
    inner join sys.tables b on a.parent_object_id = b.object_id
    inner join sys.columns c on a.parent_object_id = c.object_id 
                                and a.parent_column_id = c.column_id
where a.parent_object_id = object_id('dbo.TestConstraintsName')
    and c.name = 'createdate'
    and a.type = 'D'

if @default_constraints_name is not null
begin
    
    --删除约束
    exec('ALTER TABLE dbo.TestConstraintsName
          DROP CONSTRAINT '+@default_constraints_name)

    --重定义字段
    ALTER TABLE dbo.TestConstraintsName
    ALTER COLUMN createdate DATETIME 

    --按照原始名字重定义约束
    EXEC('ALTER TABLE dbo.TestConstraintsName
          ADD CONSTRAINT '+@default_constraints_name+' DEFAULT GETDATE() FOR createdate ')
end
else
begin
    --如果字段上不存在约束，直接重定义字段
    ALTER TABLE dbo.TestConstraintsName
    ALTER COLUMN createdate DATETIME 
end
GO