SQL SERVER 2008テーブルと制約の作成メンテナンス
- ----
- DECLARE @fk_name VARCHAR(50)
- DECLARE @fk_schName VARCHAR(50)
- DECLARE @obj_name VARCHAR(50)
- DECLARE @obj_schName VARCHAR(50)
- DECLARE @sqlstring VARCHAR(5000)
- DECLARE myCursor Cursor FOR SELECT sys.foreign_keys.name AS fk_name,sys.schemas.name AS fk_schName,sys.objects.name AS obj_name,schemas_1.name AS obj_schName FROM sys.foreign_keys INNER JOIN sys.objects ON sys.foreign_keys.parent_object_id=sys.objects.object_id INNER JOIN sys.schemas ON sys.foreign_keys.schema_id =sys.schemas.schema_id INNER JOIN sys.schemas As Schemas_1 ON sys.objects .schema_id=schemas_1.schema_id
- OPEN myCursor
- FETCH FROM myCursor INTO @fk_name,@fk_schName,@obj_name,@obj_schName
- WHILE @@FETCH_STATUS =0
- BEGIN
- SET @sqlstring='ALTER TABLE [' + @obj_schName +'].['+ @obj_name + '] DROP CONSTRAINT ' + @fk_name
- EXEC(@sqlstring)
- FETCH FROM myCursor INTO @fk_name,@fk_schName,@obj_name,@obj_schName
- END
- DEALLOCATE myCursor
-
- ---- , ,
- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Customers].[Customer]') AND type IN (N'U'))
- DROP TABLE Customers.Customer
- Go
- CREATE TABLE Customers.Customer
- (CustomerID VARCHAR(30) NOT NULL, ----
- CompanyName VARCHAR(100) NOT NULL, ----
- ContactID VARCHAR(20) NOT NULL, ---- ID
- CustomerPrimaryAddress VARCHAR(20) NULL, ---- ( )
- CreateDate DATE NULL, ----
- EmailAddress VARCHAR(50) SPARSE NULL, ---- Email ----
- CONSTRAINT ck_createdate CHECK (CreateDate>'2012-02-01'), ---- CHECK ,
- CONSTRAINT Pk_Customer PRIMARY KEY (CustomerID)) ----
- Go
-
- ---- , , ( CHECK (ck_createdate))
- IF EXISTS(SELECT * FROM sys.check_constraints WHERE object_id=OBJECT_ID(N'[Customers].[ck_createdate]') AND parent_object_id=OBJECT_ID(N'[Customers].[Customer]'))
- ALTER TABLE Customers.Customer DROP CONSTRAINT ck_createdate
- GO
- ---- :
- ---- CHECK , (WITH CHECK)
- ----ALTER TABLE Customers.Customer WITH CHECK ADD CONSTRAINT ck_createdate CHECK (CreateDate>='2012-02-01') ---- CHECK
- ---- CHECK , (WITH NOCHECK)
- ALTER TABLE Customers.Customer WITH NOCHECK ADD CONSTRAINT ck_createdate CHECK (CreateDate>='2012-02-01') ---- CHECK
-
- ---- , ,
- IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[Customers].[CustomerAddress]') AND type IN (N'U'))
- DROP TABLE Customers.CustomerAddress
- Go
- CREATE TABLE Customers.CustomerAddress
- (CustomerAddressID VARCHAR(30) NOT NULL, ---- ID
- AddressTypeID VARCHAR(2) NOT NULL, ---- ID ( :AddressTypeID VARCHAR(2) NOT NULL CONSTRAINT ck_addresstype CHECK (AddressTypeID IN ('01','02','03'))
- AddressLine1 VARCHAR(50) NOT NULL, ---- 1
- AddressLine2 VARCHAR(50) SPARSE NULL, ---- 2
- City VARCHAR(30) NOT NULL, ----
- StateProvince VARCHAR(30) NOT NULL, ----
- Country VARCHAR(30) NOT NULL, ----
- CustomerID VARCHAR(30) NOT NULL, ----
- CONSTRAINT ck_addresstype CHECK (AddressTypeID IN ('01','02','03')), ---- CHECK , AddressType
- CONSTRAINT pk_CustomerAddress PRIMARY KEY (CustomerAddressID), ----
- CONSTRAINT fk_customeraddress FOREIGN KEY (CustomerID) REFERENCES Customers.Customer(CustomerID)) ----
- Go
-
- ----
- IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[Orders].[OrderHeader]') AND type IN (N'U'))
- DROP TABLE Orders.OrderHeader
- GO
-
- CREATE TABLE Orders.OrderHeader
- (OrderID VARCHAR(30) NOT NULL, ---- ID
- OrderDate DATE NOT NULL, ----
- CustomerID VARCHAR(30) NOT NULL, ----
- EmployeeID VARCHAR(30) NULL, ----
- PayID VARCHAR(30) NULL, ----
- SALEID BIT NOT NULL CONSTRAINT dt_saleid DEFAULT(0), ---- (0: ,1: )
- Memo VARCHAR(500) SPARSE NULL, ----
- CONSTRAINT pk_orderheader PRIMARY KEY (OrderID),
- CONSTRAINT fk_orderheadercustomer FOREIGN KEY (CustomerID) REFERENCES Customers.Customer(CustomerID))
- Go
-
- ----
- IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[Orders].[OrderDetails]') AND type IN (N'U'))
- DROP TABLE Orders.OrderDetails
- Go
-
- CREATE TABLE Orders.OrderDetails
- (ID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(), ---- ID
- OrderID VARCHAR(30) NOT NULL, ---- ID
- ProductID VARCHAR(40) NOT NULL, ---- ID
- Quantity DECIMAL(12,6) NOT NULL CHECK (Quantity>0), ----
- Price MONEY NOT NULL CHECK (Price>0), ----
- TAX MONEY NOT NULL CONSTRAINT dt_tax DEFAULT(0) CHECK(TAX>=0), ----
- [Money] AS (Quantity+Price+TAX), ----
- DispatchDate DATE NULL, ----
- Present BIT NOT NULL CONSTRAINT dt_present DEFAULT(0), ----
- CustomerAddressID VARCHAR(30) NOT NULL, ----
- CONSTRAINT pk_orderdetails PRIMARY KEY(ID),
- CONSTRAINT fk_orderdetailsorder FOREIGN KEY (OrderID) REFERENCES Orders.OrderHeader (OrderID),
- CONSTRAINT fk_orderdetailsaddress FOREIGN KEY (CustomerAddressID) REFERENCES Customers.CustomerAddress(CustomerAddressID))
- Go
-
- ---
- IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[Products].[ProductType]') AND type IN (N'U'))
- DROP TABLE Products.ProductType
- GO
-
- CREATE TABLE Products.ProductType
- (ProductTypeID VARCHAR(30) NOT NULL,
- ProductTypeName VARCHAR(50) NOT NULL,
- CONSTRAINT pk_producttype PRIMARY KEY (ProductTypeID))
- Go
-
- ----
- IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[Products].[Product]') AND type IN (N'U'))
- DROP TABLE Products.Product
- Go
-
- CREATE TABLE Products.Product
- (ProductID VARCHAR(30) NOT NULL,
- ProductName VARCHAR(50) NOT NULL,
- ProductStandard VARCHAR(50) NULL,
- UnitName VARCHAR(20) NOT NULL,
- ModifiyDate DATE NOT NULL CONSTRAINT dt_modifiydate DEFAULT(getdate()),
- CONSTRAINT pk_product PRIMARY KEY (productID))
- GO
-
- ----
- IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[Products].[ProductPicture]') AND type IN (N'U'))
- DROP TABLE Products.ProductPicture
- Go
-
- CREATE TABLE Products.ProductPicture
- (ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
- ProductID VARCHAR(30) NOT NULL,
- ProductPicture VARBINARY(MAX) FILESTREAM NULL,
- CONSTRAINT pk_productpicture PRIMARY KEY (ID),
- CONSTRAINT fk_productpicture FOREIGN KEY (ProductID) REFERENCES Products.Product(ProductID))
- GO
-
- ----
- IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[HumanResources].[Employee]') AND type IN (N'U'))
- DROP TABLE HumanResources.Employee
- GO
-
- CREATE TABLE HumanResources.Employee
- (EmployeeID VARCHAR(30) NOT NULL,
- FirstName VARCHAR(50) NOT NULL,
- MiddleName VARCHAR(50) SPARSE NULL,
- LastName VARCHAR(50) NOT NULL,
- JobTitle VARCHAR(30) NULL,
- BirthDay DATE NULL CONSTRAINT ck_birthday CHECK (DATEDIFF(YEAR,Birthday,GETDATE())>=18),
- HireDate DATE NOT NULL CONSTRAINT dt_hiredate DEFAULT(GETDATE()),-- CONSTRAINT ck_hiredate CHECK (DATEDIFF(YEAR,HireDate,(CASE WHEN Birthday IS NULL THEN DATEADD(YEAR,18,HireDate) ELSE Birthday END))>=18),
- CONSTRAINT pk_employee PRIMARY KEY (EmployeeID))
- GO
-
-
- ----
- IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[HumanResources].[EmployeeAddress]') AND type IN (N'U'))
- DROP TABLE HumanResources.EmployeeAddress
- GO
-
- CREATE TABLE HumanResources.EmployeeAddress
- (AddressID VARCHAR(30) NOT NULL CONSTRAINT pk_employeeaddress PRIMARY KEY (AddressID),
- AddressType VARCHAR(2) NOT NULL CONSTRAINT ck_addresstype CHECK (AddressType IN ('01','02','03')),
- AddressLine1 VARCHAR(50) NOT NULL,
- AddressLine2 VARCHAR(50) SPARSE NULL,
- AddressLine3 VARCHAR(50) SPARSE NULL,
- City VARCHAR(50) NOT NULL,
- StateProvince VARCHAR(50) NOT NULL,
- Country VARCHAR(30) NOT NULL,
- EmployeeID VARCHAR(30) NOT NULL CONSTRAINT fk_employeeaddress FOREIGN KEY (EmployeeID) REFERENCES HumanResources.Employee(EmployeeID))
- GO
-
- ---- OrderHeader
- IF EXISTS(SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N'[Orders].[fk_orderheaderemployee]') AND parent_object_id=OBJECT_ID(N'[Orders].[OrderHeader]'))
- ALTER TABLE Orders.OrderHeader DROP CONSTRAINT fk_orderheaderemployee
- GO
- ALTER TABLE Orders.OrderHeader WITH CHECK ADD CONSTRAINT fk_orderheaderemployee FOREIGN KEY (EmployeeID) REFERENCES HumanREsources.Employee(EmployeeID)
- GO
-
- ----
- IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[LookupTables].[Country]') AND type IN (N'U'))
- DROP TABLE LookupTables.Country
- GO
- CREATE TABLE LookupTables.Country
- (CountryID VARCHAR(30) NOT NULL,
- CountryName VARCHAR(70) NOT NULL UNIQUE,
- CONSTRAINT pk_country PRIMARY KEY (CountryID))
- GO
-
- ---- CustomerAddress
- IF EXISTS(SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N'[Customers].[fk_customeraddresscountry]') AND parent_object_id=OBJECT_ID(N'[Customers].[CustomerAddress]'))
- ALTER TABLE Customers.CustomerAddress DROP CONSTRAINT fk_customeraddresscountry
- GO
- ALTER TABLE Customers.CustomerAddress WITH CHECK ADD CONSTRAINT fk_customeraddresscountry FOREIGN KEY (Country) REFERENCES LookupTables.Country(CountryID)
- GO
- ---- EmployeeAddress
- IF EXISTS(SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N'[HumanResources].[fk_employeeaddresscountry]') AND parent_object_id=OBJECT_ID(N'[HumanResources].[EmployeeAddress]'))
- ALTER TABLE HumanResources.EmployeeAddress DROP CONSTRAINT fk_employeeaddresscountry
- GO
- ALTER TABLE HumanResources.EmployeeAddress WITH CHECK ADD CONSTRAINT fk_employeeaddresscountry FOREIGN KEY(Country) REFERENCES LookupTables.Country(CountryID)
- GO
-
- ---- Product , ProductTypeID ,
- ALTER TABLE Products.Product ADD ProductTypeID VARCHAR(30) NOT NULL
- GO
- IF EXISTS(SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N'fk_producttype') AND parent_object_id=OBJECT_ID(N'[Products].[Product]'))
- ALTER TABLE Products.Product DROP CONSTRAINT fk_producttype
- GO
- ALTER TABLE Products.Product WITH CHECK ADD CONSTRAINT fk_producttype FOREIGN KEY(ProductTypeID) REFERENCES Products.ProductType
- GO
-
- ---- OrderDetails ProductID ,
- ALTER TABLE Orders.OrderDetails ALTER COLUMN ProductID VARCHAR(30) NOT NULL
- GO
- IF EXISTS(SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N'fk_orderdetailsproduct') AND parent_object_id=OBJECT_ID(N'[Orders].[OrderDetails]'))
- ALTER TABLE Orders.OrderDetails DROP CONSTRAINT fk_orderdetailsproduct
- GO
- ALTER TABLE Orders.OrderDetails WITH CHECK ADD CONSTRAINT fk_orderdetailsproduct FOREIGN KEY(ProductID) REFERENCES Products.Product(ProductID)