`

不要通过CREATE TABLE语句来创建default constraint

阅读更多

The reason why you should not use CREATE TABLE to create the default constraint is, system will generate a random constraint name for you. And when you drop it, your sql will be only workable in either dev/uat/prod db, but not all 3 dbs.

 

http://msdn.microsoft.com/en-us/library/aa175912(SQL.80).aspx

 

Working with Default Constraints

Ron Talmage

There's something bothersome about the way SQL Server Enterprise Manager scripts out tables. The default constraints are listed after the CREATE TABLE statement, and the default constraints are named something most of us would never do. In this month's column, Ron Talmage shows how you can generate your own script for creating a table that places default constraints inside the CREATE TABLE statement, giving it a more natural look than Enterprise Manager's.

Default constraints are a special case of column defaults. A column default is some value or function that the column will take when an INSERT statement doesn't explicitly assign a particular value. In other words, the column default is what the column will get as a value by default. In T-SQL, there are really two kinds of column-level defaults: default constraints and bound defaults.

Default constraints是那种标准的一列一个的那种最普通,我们最常用的那种constraint

Default constraints are ANSI-standard constraints you can assign to a column either with the CREATE TABLE or ALTER TABLE statements. Although default constraints have names, the CREATE TABLE statement doesn't let you do it, you have to let SQL Server construct a name. For example, the following CREATE TABLE statement creates a default for the cust_name column, and doesn't name it:

[法一]

通过CREATE TABLE语句来创建default constraint --- 这个default constraint就没有name

 

CREATE TABLE dbo.Customers (

  cust_id INT NOT NULL PRIMARY KEY,

  cust_name VARCHAR(30) NOT NULL DEFAULT 'new customer',

last_updated_by sysname)

The last_updated_by column doesn't have a default, so we can add it with the ALTER TABLE statement, but with ALTER TABLE, we must name the constraint:

[法二]

通过ALTER TABLE命令来创建default constraint – 可以增加constraint 名字 def_last_updated_by

 

ALTER TABLE dbo.Customers

  ADD CONSTRAINT def_last_updated_by

  DEFAULT SUSER_SNAME()

FOR last_updated_by

 

[法三] 通过CREATE TABLE来创建named default constraint, 需要使用这种语法。

CREATE TABLE Customers

(

cust_id INT NOT NULL PRIMARY KEY,

cust_name VARCHAR(30) NOT NULL

CONSTRAINT df_Customers_cust_name DEFAULT 'new customer',

last_updated_by sysname

)

 

 

In general, when you don't name a default constraint, SQL Server will assign one. In the first example, SQL Server assigned DF__Customers__cust___151B244E as the default constraint name for cust_name, not terribly elegant.

Bound Defaults 是那种独立的default constraint, 创建一次,多次绑定到不同的列。

1)                       ANSI 标准

2)                       必须要存储过程来处理

3)                       所以建议直接忘记

Bound defaults are independently defined with the T-SQL CREATE DEFAULT statement. You can then bind them to columns or to user-defined data types using the system stored procedure sp_bindefault and remove them with sp_unbindefault. An advantage of bound defaults is that you can define a bound default once and then bind it to many columns. The disadvantages are that bound defaults aren't ANSI standard (they're leftover from Sybase days) and that using system stored procedures rather than true DDL to manage them is somewhat awkward. I'd recommend that you avoid them and use ANSI standard default constraints instead.

You probably know that, in the absence of a default constraint (or a bound default), SQL Server will attempt to insert NULL into a column that isn't otherwise provided a value in the INSERTstatement. For that reason, column defaults are very useful when combined with columns that don't allow NULL. If you define a column as NOT NULL, but give the column a default, then INSERT statements don't need to explicitly assign values to the column in order to insert a new row.

The scripting problem

Often it's useful to store table definitions as text file scripts, for purposes of documenting a database or as part of a disaster recovery plan. You can use SQL Server's 7.0 Enterprise Manager to script out a table by right-clicking over the table name and choosing "All Tasks," and then "Generate SQL Scripts" from the menu. If you don't choose any special options, and then click the Preview button, you'll see a CREATE TABLE statement to recreate the table, but you won't see the default constraints listed in the statement. To see the column defaults, you'll have to click on the Options tab and check the "Script PRIMARY Keys, FOREIGN keys, Defaults, and Check Constraints" checkbox. Unfortunately, the scripting utility puts the default constraints at the end in an ALTER TABLE statement. For the previous table, we see the following:

 

CREATE TABLE [dbo].[Customers] (

  [cust_id] [int] NOT NULL ,

  [cust_name] [varchar] (30) NULL ,

  [last_updated_by] [sysname] NOT NULL

)

GO

ALTER TABLE [dbo].[Customers] WITH NOCHECK ADD

  CONSTRAINT [DF__Customers__cust___151B244E]

  DEFAULT ('new customer') FOR [cust_name],

  CONSTRAINT [def_last_updated_by] DEFAULT

  (suser_sname()) FOR [last_updated_by],

  PRIMARY KEY  CLUSTERED

  ([cust_id])  ON [PRIMARY]

GO

 

There are a number of problems here. First of all, the default constraints are named and placed after the CREATE TABLE statement in an ALTER TABLE command. Most people, including myself, just put default constraints inline, in the CREATE TABLE statement. Consequently, the scripted table definition often won't match the original statement. This can be problematic. If you want to create a copy of that table in the same database with the same column defaults, you'll have to change those constraint namesin an editorone by one. A further problem is that the scripting utility isn't configurable. For example, if you remove the square brackets around column names, then the Query Analyzer can help pinpoint column names that are SQL keywordssomething I like to check for. To remove those brackets, you'll have to edit them out yourself.

ANSI views to the rescue

You can use gp_TableScript stored procedure (available in the accompanying Download file) to generate a script that will use the ANSI COLUMNS view to extract all of the information necessary to recreate a table, and place the column defaults in the CREATE TABLE statement. Because a CREATE TABLE statement might easily be larger than 8,000 bytes, the stored procedure just uses PRINT to generate the script into the output window of Query Analyzer. After generating the initial boilerplate part of the CREATE TABLE, gp_TableScript sets up a WHILE loop to iterate through the COLUMNS view. As it loops through the view, it takes each column, in column order, and prints out the appropriate statement.

 

In the code the @cmd is loaded first with the column name:

 

SET @cmd = (SELECT CHAR(9) +

SUBSTRING(t1.column_name,1,60) + CHAR(9)

then with the data type:

 

RTRIM(t1.data_type)

 

Unfortunately, the view won't tell us whether the column has the identity property, so the stored procedure uses the COLUMNPROPERTY() function instead:

 

CASE WHEN COLUMNPROPERTY(OBJECT_ID(t1.table_name),

t1.column_name, 'isidentity') = 1

THEN ' identity' ELSE '' END

Then if the column is a character, get its length:

 

CASE WHEN t1.data_type IN('char', 'varchar',

'nchar', 'nvarchar') THEN

'(' + RTRIM(character_maximum_length) + ')'

Or if the column is numeric, get its precision and scale:

 

WHEN t1.data_type IN('decimal', 'numeric')

THEN '(' + RTRIM(numeric_precision) + ',' +

RTRIM(numeric_scale) + ')'

ELSE '' END

Then add whether the column should be NULL or not:

 

CASE WHEN Is_Nullable = 1 THEN 'NULL'

ELSE 'NOT NULL' END

Finally, get the column default constraint value from the view:

 

CASE WHEN DATALENGTH(t1.column_default) > 0

THEN 'DEFAULT ' + t1.column_default ELSE '' END

When you use the stored procedure against the preceding table, like so:

 

gp_TableScript 'customers'

the output of the stored procedure is

 

IF OBJECT_ID('dbo.Customers') IS NOT NULL

  DROP TABLE dbo.Customers

go

CREATE TABLE dbo.Customers(

  cust_id int NOT NULL,

  cust_name varchar(30) NOT NULL DEFAULT

  ('new customer'), last_updated_by nvarchar(256)

  NOT NULL DEFAULT (suser_sname())

 

The stored procedure inserts tabs but can't make the columns line up precisely. I use it most often to generate scripts for documenting table definitions where column defaults are important. You can customize it further to add square brackets if you want, and to detect bound defaults or check constraints.

 

Dropping a default constraint – Very important

Default constraints can come back to bite you when you drop a column. For instance, attempting to drop the cust_name column from the Customers table in the previous example:

 

ALTER TABLE Customers

DROP COLUMN cust_name

 

fails because the default constraint hasn't been removed. When you use the visual data tools in SQL Server to remove the column, that's all taken care of for you, but when you're writing your own T-SQL, this can be a stumbling block. Unfortunately, the name of the column default constraint isn't kept in the ANSI COLUMNS view, so you must go back to the system tables to find the name. One way to do it is to use sysobjects to get the default constraint names for the table, joined with sysconstraints on the constraint id, limited by the appropriate column id from syscolumns. Then we need to embed it into an ALTER TABLE statement so that the default constraint will be dropped. Here's an example of that approach:

 

DECLARE @defname VARCHAR(100), @cmd VARCHAR(1000)

SET @defname =

(SELECT name

FROM sysobjects so JOIN sysconstraints sc

ON so.id = sc.constid

WHERE object_name(so.parent_obj) = Customers'

AND so.xtype = D'

AND sc.colid =

 (SELECT colid FROM syscolumns

 WHERE id = object_id(dbo.Customers') AND

 name = cust_name'))

SET @cmd = ALTER TABLE Customers DROP CONSTRAINT + @defname

EXEC(@cmd)

 

You could also make this a stored procedure to drop a particular column on a table. However, while this handles default constraints, it doesn't handle all types of constraints. Bound defaults, check constraints, and foreign key constraints can all prevent an ALTER TABLE from dropping a column.

Download RON100.SQL

Ron Talmage is a independent database consultant with Prospice, LLC. Ron is author of Microsoft SQL Server 7.0 Administrator's Guide (Prima Publishing), is a SQL Server MVP, the PASS newsletter editor, and is current president of the Pacific Northwest SQL Server Users Group. RonTalmage@compuserve.com.

To find out more about Microsoft SQL Server Professional and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the January 2000 issue of Microsoft SQL Server Professional. Copyright 2000, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Microsoft SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.

 

 

分享到:
评论

相关推荐

    使用SQL语句创建数据库与创建表

    创建表则使用CREATE TABLE语句。以创建"student"、"course"和"SC"表为例: 创建"student"表: ```sql CREATE TABLE student ( id CHAR(9), name VARCHAR(20), gender CHAR(1), birthday DATE, phone CHAR(6), ...

    实验二--通过SQL语句创建与管理数据表.pdf

    我们使用了 CREATE TABLE 语句来创建表,例如: ``` CREATE TABLE Student ( Sno char(8) PRIMARY KEY, Sname varchar(8) NOT NULL, Sex char(2) NOT NULL, Birth smalldatetime NOT NULL, Classno char(3) ...

    SQL语句创建表.doc

    语法:USE 数据库名 CREATE TABLE 表名 (列名 类型(大小) DEFAULT'默认值' CONSTRAINT 约束名 约束定义, ... ...); 例如:CREATE TABLE Persons (Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName ...

    SQL语句创建表常用操作

    本文将详细介绍SQL语句创建表时的一些常用操作。 首先,创建数据库是建立表的前提。以下是一个创建数据库的例子: ```sql USE master; -- 切换到master数据库 GO IF EXISTS (SELECT * FROM sysdatabases WHERE ...

    达梦数据库建表操作SQL语句大全.pdf

    同样,`SubModColumn` 函数用于更改字段类型,通过执行ALTER TABLE语句来更改现有字段的定义。 17. 检查表是否存在: 通过SQL语句 `select count(*) as dida from sysobjects where id = object_id(N'[所有者]....

    数据库基本sql语句.pdf

    创建法 2:在创建表后通过 ALTER TABLE 语句指定约束。 ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (列名); ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (列名); ALTER TABLE 表名 ADD CONSTRAINT ...

    实验二__通过SQL语句创建及管理数据表.pdf

    删除操作可以使用DROP TABLE语句,例如: ```sql DROP TABLE Student; DROP TABLE Course; DROP TABLE SC; ``` 二、创建表 在实验中,我们使用SQL语句创建了Student表、Course表和SC表。创建表的语句如下: ```...

    用SQL语句创建表表的连接等实验操作

    - **创建索引**:通过`CREATE INDEX`语句创建索引以提高查询速度。 ```sql CREATE INDEX 索引名 ON 表名 (列名 [DESC]); ``` 例如,创建基于年龄的降序索引: ```sql CREATE UNIQUE INDEX Sage ON Student ...

    SQl 语句(常见) 新建,删除,修改表结构

    在SQL中,使用`CREATE TABLE`语句来定义表的结构,包括字段名、数据类型、长度、是否允许为空以及默认值等。例如: ```sql CREATE TABLE [表名] ( [自动编号字段] INT IDENTITY (1,1) PRIMARY KEY, [字段 1] ...

    经典SQL语句大全_主外键_约束

    在本篇文章中,我们将深入探讨SQL中的一个重要概念——约束(Constraint),以及如何通过SQL语句来创建和管理这些约束。 约束是确保数据库中数据完整性的重要手段。在Microsoft SQL Server中,有五种主要的约束类型...

    T-SQL 基本语句归纳总结(SQL server 2008)

    CREATE TABLE 语句用于创建一个新的数据表。其基本语法格式为: CREATE TABLE [database_name.[schema_name].| schema_name.]table_name ( { <column_definition> | <computed_column_definition> } [ ,...n ] ) ...

    mysql和sqlserver的比较

    例如,MySQL 的 `CREATE TABLE` 语句可以直接创建主键(PRIMARY KEY),而 SQL Server 中需要单独指定 PRIMARY KEY 约束。 **主键(PRIMARY KEY)**: - MySQL 中创建主键: ```sql CREATE TABLE table_name ( ...

    alter sql 语句实例

    它可以通过更改、添加、删除列和约束,或者通过启用或禁用约束和触发器来更改表的定义。 ALTER TABLE 语句有多种子形式,包括: 1. ADD COLUMN:向表中添加一个新的字段。语法与 CREATE TABLE 中的语法相同。 ...

    数据库默认值约束默认值对象检查约束规则完整性实现PPT学习教案.pptx

    默认值对象是指使用 CREATE DEFAULT 语句定义的默认值对象,可以绑定到列或用户定义数据类型。语法格式为: ```sql CREATE DEFAULT default_name AS constant_expression; ``` 例如,在 student 数据库中定义 def_...

    sql常用语句练习,数据库类通用

    创建视图是通过`CREATE VIEW`语句实现的,它可以简化复杂的查询或提供数据的安全访问控制。例如: ```sql CREATE VIEW a AS (SELECT * FROM s WHERE sname='huawei'); CREATE VIEW b (a,b,c,d) AS (SELECT * FROM s ...

    用sql语句创建BBS数据库及表

    本实验主要涉及如何使用SQL语句创建一个名为"BBSDB"的BBS(Bulletin Board System,电子公告板)数据库,并构建其所需的用户表、主贴表、回帖表和版块表,同时添加各种约束来确保数据的完整性和一致性。 首先,我们...

    SQL语句集锦完整版

    #### CREATETABLE语句 用于创建新表。基本语法为`CREATE TABLE table_name (column1 datatype,...);`。 #### DROPTABLE语句 用于删除表。基本语法为`DROP TABLE table_name;`。 #### ALTERTABLE语句 用于修改现有...

    oracle语句格式

    create table table_name (column_name datatype [constraint constraint_name default default_expression…]); ``` 或间接的,通过复制现有表: ```sql create table table_name as subquery; ``` 表的维护包括...

    Oracle基本语句

    在 Oracle 中,创建表需要使用 CREATE TABLE 语句,例如: ```sql CREATE TABLE classes ( id NUMBER(9) NOT NULL PRIMARY KEY, classname VARCHAR2(40) NOT NULL ); ``` 这条语句创建了一个名为 classes 的表,...

    sql常用sql语句

    在SQL中,创建数据库是一项基本操作,通过`CREATE DATABASE`命令来实现。创建数据库时,可以指定数据库的名字、初始大小、最大大小以及增长方式等属性。 ##### 示例代码解析 ```sql if exists (select * from sys....

Global site tag (gtag.js) - Google Analytics