使用MS_SQL建库，建表，添加约束

use master go if exists(select * from sysdatabases where name = 'Asset') drop database Asset create database Asset on primary ( name = 'Asset_data', filename = 'E:\Asset_data.mdf', size = 5mb, filegrowth = 10% ) log on ( name = 'Asset_log', filename = 'E:\Asset_log.ldf', size = 5mb, filegrowth =10% ) go use Asset go if exists(Select * from sysobjects where name = 'AssetType') drop table AssetType create table AssetType ( TypeCode nvarchar(50) primary key not null, TypeName nvarchar(50) not null ) go if exists(Select * from sysobjects where name = 'AssetInf') drop table AssetInf create table AssetInf ( Id int identity(1,1) not null, AssetCode nvarchar(50) primary key not null, SelfCode nvarchar(50) not null, AssetName nvarchar(50)not null, TypeCode nvarchar(50) not null, Model nvarchar(50) , Manufacturer nvarchar(225), OutFacDate datetime, StorageCode nvarchar(50), DeptNo nvarchar(50), [State] nvarchar(50), AddMode nvarchar(50), Num int, Unit nvarchar(50), Price money, Amount money, OriginValue money, DepreciationTotal money, DepreciationMode nvarchar(50) not null, DepreciationMonths int, DistillMonths int, DepValByMonth money, IndentRemainValue money, OtherDesc nvarchar(500) ) go if exists(Select * from sysobjects where name = 'DecrementInf') drop table DecrementInf create table DecrementInf ( Id int identity(1,1) primary key not null, AssetCode nvarchar(50), Num Int not null, [Date] Datetime not null, Mode nvarchar(50), Reason nvarchar(225), CleanIncome money, CleanRate money, Handler nvarchar(50), [Description] nvarchar(500) ) go if exists(Select * from sysobjects where name = 'DepreciationInf') drop table DepreciationInf create table DepreciationInf ( Id int primary key identity(1,1) not null, DeptNo nvarchar(50) not null, AssetCode nvarchar(50) not null, DepreciationYear int, DepreciationMonth int , DepreciationValue money ) go if exists(Select * from sysobjects where name = 'DeptmentInf') drop table DeptmentInf create table DeptmentInf ( DeptNo nvarchar(50) primary key not null, DeptName nvarchar(50) not null ) go if exists(Select * from sysobjects where name = 'RoleInf') drop table RoleInf create table RoleInf ( Id int primary key identity(1,1) not null, RoleName varchar(50) not null, RoleDesc nvarchar(225), ) go if exists(Select * from sysobjects where name = 'UserInf') drop table UserInf create table UserInf ( UserId nvarchar(20)primary key not null, Pwd varchar(50) not null, RealName nvarchar(20) not null, RoleId int not null, DeptNo nvarchar(50) not null ) go if exists(Select * from sysobjects where name = 'Storage ') drop table Storage create table Storage ( StorageCode nvarchar(50) primary key not null, StorageName varchar(50), [Local] nvarchar(500) not null, ) go if exists(Select * from sysobjects where name = 'Menu') drop table Menu create table Menu ( Id int primary key identity(1,1) not null, MenuItem varchar(50) not null, ParentId int not null, MenuOrder int not null ) go if exists(Select * from sysobjects where name = 'RoleRight') drop table RoleRight create table RoleRight ( Id int primary key identity(1,1)not null, MenuItemId int not null, RoleId int not null ) go if exists(Select * from sysobjects where name = 'LendInf ') drop table LendInf create table LendInf ( Id int primary key identity(1,1) not null, AssetCode nvarchar(50) not null, Num int not null, LendCompany nvarchar(500) not null, LendDate datetime not null, ExpectReturnDate datetime not null, ReturnDate datetime, ExpectIncome money, Income money, Handler nvarchar(20) not null, Description nvarchar(500) ) go if exists(Select * from sysobjects where name = 'FixInf ') drop table FixInf create table FixInf ( Id int primary key identity(1,1) not null, AssetCode nvarchar(50) not null, Num int not null, FixDate datetime not null, ExpectCompleteDate datetime not null, CompleteDate datetime , ExpectFixRate money, FixRate money, FixCompany nvarchar(50), Reason nvarchar(255), FixStatus nvarchar(500), Handler nvarchar(20) not null, [Description] nvarchar(500) ) go Alter table AssetInf Add Constraint DF_Price default(0) for Price, Constraint DF_Amount default(0) for Amount, Constraint DF_OriginValue default(0) for OriginValue, Constraint DF_DepreciationTotal default(0) for DF_DepreciationTotal , Constraint DF_DepreciationMonths default(0) for DF_DepreciationMonths, Constraint DF_DistillMonths default(0) for DF_DistillMonths , Constraint DF_DepValByMonths default(0) for DF_DepValByMonths, Constraint DF_IndentRemainValue default(0) for DF_IndentRemainValue go

附：

主外键关联：

Add Constraint FK_RoleId foreign key(UserRoleId) references RoleInf(id)