既存のテーブルにコンストレイントを追加(練習1)

1、データベーステーブルの作成:

--         E_Market，   E_Market       
USE E_Market
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),--  
	UserAddress VARCHAR(200),--    
	Phone VARCHAR(20) NOT NULL--    
)
GO

--     
IF EXISTS(SELECT * FROM sysobjects WHERE name='OrderInfo')
DROP TABLE OrderInfo
GO
CREATE TABLE OrderInfo
(
	OrderId INT IDENTITY(1,1) NOT NULL,--    
	UserId VARCHAR(20) NOT NULL,--  ，  UserInfo    
	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,--     ，1    ，0      ，  0
	SendGoods INT NOT NULL --    ，0  ，1  ，  0
)
GO

--       
IF EXISTS(SELECT * FROM sysobjects WHERE name='CommoditySort')
DROP TABLE CommoditySort
GO
CREATE TABLE CommoditySort
(
	SortId  INT IDENTITY(1,1) NOT NULL,--      ，  ，    
	SortName VARCHAR(50) NOT NULL--      
)
GO

--       
IF EXISTS(SELECT * FROM sysobjects WHERE name='CommodityInfo')
DROP TABLE CommodityInfo
GO
CREATE TABLE CommodityInfo
(
	CommodityId int IDENTITY(1,1) NOT NULL,--    
	SortId int NOT NULL,--      ，  
	CommodityName varchar(50) NOT NULL,--    
	Picture image ,--    
	InPrice float NOT NULL,--     
	OutPrice float NOT NULL,--      
	Amount int --     	
)
GO

--     
SELECT * FROM CommoditySort
SELECT * FROM CommodityInfo
SELECT * FROM UserInfo
SELECT * FROM OrderInfo

、上記の表に制約を追加します.

USE E_Market--            
GO
--    UserInfo    
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 DF_Gender DEFAULT(0) FOR Gender, --     “0”（ ）
	CONSTRAINT CK_Email CHECK(Email LIKE '%@%') --         “@”
GO

--      CommodityInfo    
ALTER TABLE CommodityInfo
ADD CONSTRAINT PK_CommodityId PRIMARY KEY (CommodityId)
GO

--    OrderInfo    
ALTER TABLE OrderInfo
ADD CONSTRAINT PK_OrderId PRIMARY KEY (OrderId),
	CONSTRAINT FK_UserId FOREIGN KEY(UserId) REFERENCES UserInfo(UserId), --      ：OrderInfo(UserId)       UserInfo(UserId)（  ）.
	CONSTRAINT FK_CommodityId FOREIGN KEY(CommodityId) REFERENCES CommodityInfo(CommodityId),
	CONSTRAINT DF_PayWay DEFAULT('    ') FOR PayWay, -- PayWay      
	CONSTRAINT CK_Confirm CHECK(Confirm=0 OR Confirm=1), 
	CONSTRAINT DF_Confirm DEFAULT(0) FOR Confirm, --     0
	CONSTRAINT CK_SendGoods CHECK(SendGoods =0 OR SendGoods=1),
	CONSTRAINT DF_SendGoods DEFAULT(0) FOR SendGoods
GO

--[1]      ：  、              、        
--[2]                  

--          
ALTER TABLE CommoditySort
ADD CONSTRAINT PK_SortId PRIMARY KEY (SortId)
GO

-- CommodityInfo           
ALTER TABLE CommodityInfo
ADD CONSTRAINT FK_SortId FOREIGN KEY (SortId) REFERENCES CommoditySort(SortId)
GO