不多说，直接上代码，在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
