数据库用seq语句从创建数据库到给表添加约束条件

不多说，直接上代码，在sql server2010 运行成功

USE master
GO
IF EXISTS (SELECT * FROM SYSDATABASES WHERE name = 'E_NEW')
DROP DATABASE E_NEW
CREATE DATABASE E_NEW
ON PRIMARY
(
NAME = 'E_NEW_DATA',
FILENAME = 'D:\SqlServer\E_NEW_DATA.mdf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 15%
)
LOG ON
(
NAME = 'E_NEW_LOG',
FILENAME = 'D:\SqlServer\E_NEW_LOG.ldf',
SIZE = 5MB,
FILEGROWTH = 0
)


SELECT * FROM SYS.databases WHERE name = 'E_NEW'
SELECT DATABASEPROPERTYEX('E_NEW', 'STATUS')


EXEC sp_detach_db @dbname = 'E_NEW'
go
EXEC sp_attach_db @dbname = 'E_NEW',
@filename1 = 'D:\SqlServer\E_NEW_DATA.mdf',
@filename2 = 'D:\SqlServer\E_NEW_LOG.ldf'
GO

USE E_NEW
GO
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name = 'UserInfo')
DROP TABLE UserInfo
GO
CREATE TABLE UserInfo
(
UserId varchar(20) NOT NULL,
UserPwd varchar(50) NOT NULL,
UserName varchar(50) NOT NULL,
Gender int NOT NULL,
Email varchar(50),
UserAdress nvarchar(200),
Phone varchar(20) NOT NULL
)
GO


--给表添加约束
USE E_NEW
GO
ALTER TABLE UserInfo
ADD CONSTRAINT PK_UserId PRIMARY KEY(UserId),
CONSTRAINT CK_UserPwd CHECK(LEN(UserPwd)>= 6),
CONSTRAINT CK_Gender CHECK (Gender = 0 or Gender = 1),
CONSTRAINT DK_Gender DEFAULT(0) FOR Gender,
CONSTRAINT CK_Email CHECK(Email LIKE '%@%')
GO


--订单表
USE E_NEW
GO
CREATE TABLE OrderInfo1
(
OrderId int IDENTITY(1,1) NOT NULL,
UserId varchar(20) NOT NULL,
CommodityId int NOT NULL, --商品编号
Amount int NOT NULL,
PayMoney bigint NOT NULL,
PayWay varchar(50) NOT NULL,
OrderTime datetime NOT NULL,
Confirm int NOT NULL,
SendGoods int NOT NULL
)
GO
--添加外键约束时，注意：
--1.类型 长度必须与引用的主键的类型长度一致
--2.引用的表中必须有主键列


--为上边的订单表添加约束
USE E_NEW
GO
ALTER TABLE OrderInfo
ADD CONSTRAINT PK_OrderId PRIMARY KEY(OrderId),
CONSTRAINT FK_UserId FOREIGN KEY (UserId) REFERENCES UserInfo (UserId),
CONSTRAINT DK_PayWay DEFAULT('网上银行') FOR PayWay,
CONSTRAINT CK_Confirm CHECK (Confirm = 0 or Confirm = 1),
CONSTRAINT DK_Confirm DEFAULT(0) FOR Confirm,
CONSTRAINT CK_SendGoods CHECK (SendGoods = 0 or SendGoods = 1),
CONSTRAINT DK_SendGoods DEFAULT(0) FOR SendGoods
GO