最近想全面了解下SQL中的东西,想操作下约束 我手动建了表student 有两个字段 id,age 都为int型,其中id是主键,age的默认值是0 ,手动建完后查看脚本如下:
USE [A]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student](
[id] [int] NOT NULL,
[age] [int] NOT NULL CONSTRAINT [DF_student_age] DEFAULT ((0)),
CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]我现在想用脚本将字段age改为varchar型的,并且默认值为'0' ,我写了下面的脚步系统提示不行
alter table student alter column age [varchar] (200)这里好像要首先删除age上的约束,然后再操作,怎么写一个SQL实现我的所要呢 ??
USE [A]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student](
[id] [int] NOT NULL,
[age] [int] NOT NULL CONSTRAINT [DF_student_age] DEFAULT ((0)),
CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]我现在想用脚本将字段age改为varchar型的,并且默认值为'0' ,我写了下面的脚步系统提示不行
alter table student alter column age [varchar] (200)这里好像要首先删除age上的约束,然后再操作,怎么写一个SQL实现我的所要呢 ??
解决方案 »
- 求教! mysql如何添加外键关联
- 100分求一SQL行转列
- 急..datatime类型字段查不出来(在线等)
- 这个查询该怎么写
- 有没有对BULK INSERT比较精通的啊,我这有个文件很难处理,很急的,留个MSN或者QQ啊,谢谢!!
- 满分求助百万数据服务器配置和服务器集群相关的问题,高手进
- 一个特殊查询列表的查询语句??????
- 如何在不使用临时表的情况下对一个主键未知的表用一条SQL查出第100条到最后的所有纪录
- 谁能告诉我下面这个SQL语句的含义和用法??多谢
- 请问怎样用SQL或其它方法将几个相同的(mdb)表合并成一个表
- sql 批量插入的问题
- 这样如何使用left join 求一SQL !!
[id] [int] NOT NULL,
[age] [int] NOT NULL CONSTRAINT [DF_student_age] DEFAULT ((0)),
CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]--1
alter table [student] drop constraint [DF_student_age]
go
--2
alter table student alter column age [varchar] (200)
go
--3.
alter table [student] add constraint [DF_student_age] default '0' for age
go看错。这样
alter table [dbo].[student] drop CONSTRAINT DF_student_age
alter table [dbo].[student] alter column age varchar(200)
alter table [dbo].[student] add CONSTRAINT DF_student_age DEFAULT '' for age