sql server 建表常用语句 （包括主键，外键 等）

sql server 建表常用语句 （包括主键，外键 等）

USE [YGGL]
GO

if exists(select 1 from sysobjects where name='Salary' and type='u')
	DROP TABLE [dbo].[Salary]
GO


CREATE TABLE [dbo].[Salary](
	[EmployeeID] [char](6) NOT NULL PRIMARY KEY,
	[InCome] [float] NOT NULL,
	[OutCome] [float] NULL
)

if exists(select 1 from sysobjects where name= 'fk_DepartmentID ' and xtype= 'F ')
	ALTER TABLE dbo.Employes DROP CONSTRAINT fk_DepartmentID 
 

/****** Object:  Table [dbo].[Departments]    Script Date: 2015/11/11 9:48:06 ******/
if exists(select 1 from sysobjects where name='Departments' and type='u')	
	DROP TABLE [dbo].[Departments]
GO

CREATE TABLE [dbo].[Departments](
	[DepartmentID] [char](3) NOT NULL PRIMARY KEY ,
	[DepartmentName] [char](20) NOT NULL,
	[Note] [char](16) NULL,
)

if exists(select 1 from sysobjects where name='Employes' and type='u')	
	DROP TABLE [dbo].[Employes]
GO

CREATE TABLE [dbo].[Employes](
	[EmployeeID] [char](6) NOT NULL PRIMARY KEY,
	[Name] [char](10) NOT NULL,
	[Birthday] [datetime] NOT NULL,
	[Sex] [bit] NOT NULL,
	[Address] [char](20) NULL,
	[Zip] [char](6) NULL,
	[PhoneNumber] [char](12) NULL,
	[EmailAddress] [char](30) NULL,
	[DepartmentID] [char](3) NOT NULL
)

ALTER TABLE [dbo].[Employes]  ADD  CONSTRAINT [fk_DepartmentID] FOREIGN KEY([DepartmentID]) 
	REFERENCES [dbo].[Departments] ([DepartmentID])
GO