专注,勤学,慎思。戒骄戒躁,谦虚谨慎

just do it

导航

从一个简单的约束看规范性的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

 

 

 

posted on 2017-09-07 22:02  MSSQL123  阅读(440)  评论(2编辑  收藏  举报