创建表-各种约束条件

create table dbo.Customer
(customerid int identity(1,1),
 customername varchar(50) not null,
 creditLine smallmoney null,
 OutstandingBalance smallmoney null,
 availableCredit as (creditLine - OutStandingBalance),
 CreateDate datetime not null
)

create table dbo.Country
(CountryID int identity(1,1),
 Country varchar(50) not null
 )
go

create rule EmailValidator
as
@value like '%@%.[a-z][a-z][a-z]' or @value like '%@%.[a-z][a-z].[a-z][a-z]';

create rule EmployeeIDValidatot
as
@column like '[A-Z][0-9][0-9][0-9][A-Z][A-Z][A-Z][A-Z]';

create table dbo.CustomerAddress
(CustomerAddressID int identity(1,1),
 AddressType char(4) not null,
 PrimaryAddressFlag bit null,
 City varchar(50) not null DEFAULT 'Grand Prairie',--默认约束
 CountryID int null
 )
 
 create table #temp
 (aaa int identity(1,1),
  bbb varchar(50) not null default 'fdsfs',
  ccc varchar(10) null
 )
 
 select * from #temp
 insert into #temp(ccc)
 values('fdf')
 
 insert into #temp
 values('','ffdf')

 insert into #temp
 values('fsf','ffdf')

drop table #temp

create table dbo.Customere
(customerid int identity(1,1),
 customername varchar(50) not null unique nonclustered ,--唯一约束
 creditLine smallmoney null
 )
 
create table dbo.StateProvince
(StateProvinceId int identity(1,1) primary key, --主键约束
 StateProvince varchar(50) not null
)
create table dbo.CustomerAddress1
(CustomerAddressID int Identity(1,1),
 PrimaryAdressFlag bit not null,
 addressline varchar(30) not null,
 City varchar(50) not null Default 'Grand Prairie',
 stateProvinceid int null References dbo.StateProvince(StateProvinceID),--外键约束
 CountryID int null
)
--必须先删除CustomerAddress1表，或从CustomerAddress1中删除外检约束，才能删除StateProvince表。

drop table Country
drop table StateProvince
drop table CustomerAddress

create table dbo.StateProvince
(StateProvinceID int identity(1,1) primary key clustered,
 StateProvince varchar(50) not null)

create table dbo.Country
(CountryID int Identity(1,1) primary key clustered,
 Country varchar(50) not null)
 
create table dbo.AddressType
(AddressTypeID tinyint identity(1,1) primary key clustered,
 AddressType varchar(20) not null)
 
create table dbo.CustomerAddress
(CustomerAddressID int identity(1,1) primary key clustered,
 PrimaryAddressFlag bit not null,
 City varchar(50) not null,
 StateProvinceID int null ,
 PostalCode char(10) null,
 CountryId int null foreign key(CountryId) references dbo.Country(CountryId),
 foreign key(StateProvinceID) references dbo.StateProvince(StateProvinceID),
 AddressType tinyint null ,
 foreign key(AddressType) references dbo.AddressType(AddressTypeID)
 )
 --设置外键时，数据类型必须相同
 --设置外键时，名字可以与外键表不一样，但是为了提高可读性，一般取相同的名字
 
drop table Customer
create table dbo.customer
 (customerID int identity(1,1) primary key clustered,
  customername varchar(50) not null unique nonclustered,
  creditLine smallmoney null check (creditLine >= 0 and creditLine <= 50000),
  OutstandingBalance smallmoney null default 0,
  availableCredit as (creditLine - OutstandingBalance),
  CreateDate datetime not null default getdate()
 )

create table dbo.CustomerToCustomerAddress
(CustomerID int not null foreign key(CustomerID) references dbo.Customer(CustomerID),
 CustomerAddressID int not null foreign key(CustomerAddressID) references dbo.CustomerAddress(CustomerAddressid),
 constraint pk_CustomerToCustomerAddress primary key clustered(CustomerId,CustomerAddressID)
)

--*3.创建用户定义的类型
--使用基于T-SQL 的UDT可以在表定义中实施一致性，用公共语言运行时（CRL）UDT可以创建SQL Server没有的新数据类型

--T-SQL UDT

drop table CustomerAddress

create type udt_city
from varchar(30) not null;

create table dbo.CustomerAddress
(CustomerAddressid int identity(1,1) primary key clustered,
 addresstypeId tinyint  null foreign key(addresstypeID) references dbo.addresstype(addresstypeid),
 primaryaddressflag bit not null,
 addressline varchar(30) not null,
 city udt_city not null,
 StateProvinceid int null foreign key(stateProvinceid) references dbo.stateProvince(stateProvinceid)
)

--CLR UDT
--只有对已经明确定义了值域，并且在数据类型定义中只需最少量代码的小型离散数据类型，才应考虑使用CLR数据类型
--要使用CLR UDT ，首先必须在"SQL Server 2005外围应用配置器"中启用CLR功能。如果某一时间CLR被禁用了，所有CLR UDT不能再被访问。
--要使用编程语言（如C#）编写一个满足UDT规范的类，编译成一个动态链接库（DLL）。由sysadmin固定服务器角色的成员在SQL Server实例中注册该程序集。