· 6 years ago · Mar 07, 2019, 12:08 PM
1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
4
5SET NAMES utf8;
6
7DROP SCHEMA IF EXISTS `mydb` ;
8CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
9USE `mydb` ;
10
11-- -----------------------------------------------------
12-- Table `mydb`.`Customer_Account`
13-- -----------------------------------------------------
14DROP TABLE IF EXISTS `mydb`.`Customer_Account` ;
15
16CREATE TABLE IF NOT EXISTS `mydb`.`Customer_Account` (
17 `idCustomer_Account` VARCHAR(20) NOT NULL,
18 `Name` VARCHAR(130) NOT NULL,
19 `Billing_Street` VARCHAR(150) NULL,
20 `Billing_City` VARCHAR(30) NULL,
21 `Billing_StateProvince` VARCHAR(25) NULL,
22 `Billing_PostalCode` VARCHAR(15) NULL,
23 `Billing_Country` VARCHAR(20) NULL,
24 `Location_Type` VARCHAR(15) NULL,
25 `Parent_ID` VARCHAR(20) NULL,
26 `Parent_Name` VARCHAR(130) NULL,
27 PRIMARY KEY (`idCustomer_Account`),
28 UNIQUE INDEX `idCustomer_Account_UNIQUE` (`idCustomer_Account` ASC))
29ENGINE = InnoDB DEFAULT CHARSET=utf8;
30
31 'CONSTRAINT `Parent_ID`
32 FOREIGN KEY (`idCustomer_Account`)
33 REFERENCES `mydb`.`Customer_Account` (`idCustomer_Account`)
34 ON DELETE NO ACTION
35 ON UPDATE NO ACTION)'
36
37-- -----------------------------------------------------
38-- Table `mydb`.`Customer_Address`
39-- -----------------------------------------------------
40DROP TABLE IF EXISTS `mydb`.`Customer_Address` ;
41
42CREATE TABLE IF NOT EXISTS `mydb`.`Customer_Address` (
43 `idCustomer_Address` VARCHAR(20) NOT NULL,
44 `Name` VARCHAR(50) NOT NULL,
45 `Name_Int` INT,
46 `Address` VARCHAR(80) NULL,
47 `City` VARCHAR(30) NULL,
48 `State` VARCHAR(5) NULL,
49 `ZIP` VARCHAR(15) NULL,
50 `LinkedAccount_Name` VARCHAR(100) NULL,
51 `LinkedAccount_ID` VARCHAR(20) NULL,
52 `CADD` INT,
53 PRIMARY KEY (`idCustomer_Address`),
54 UNIQUE INDEX `idCustomer_Address_UNIQUE` (`idCustomer_Address` ASC))
55ENGINE = InnoDB DEFAULT CHARSET=utf8;
56
57-- -----------------------------------------------------
58-- Table `mydb`.`Contacts`
59-- -----------------------------------------------------
60DROP TABLE IF EXISTS `mydb`.`Contacts` ;
61
62CREATE TABLE IF NOT EXISTS `mydb`.`Contacts` (
63 `idContacts` VARCHAR(20) NOT NULL,
64 `Name` VARCHAR(50) NOT NULL,
65 `Title` VARCHAR(130) NULL,
66 `Mailing_Street` VARCHAR(110) NULL,
67 `Mailing_City` VARCHAR(30) NULL,
68 `Mailing_State` VARCHAR(25) NULL,
69 `Mailing_PostalCode` VARCHAR(20) NULL,
70 `Phone` VARCHAR(50) NULL,
71 `Account_ID` VARCHAR(20) NULL,
72 `Account_Name` VARCHAR(100) NULL,
73 PRIMARY KEY (`idContacts`),
74 UNIQUE INDEX `idContacts_UNIQUE` (`idContacts` ASC))
75ENGINE = InnoDB DEFAULT CHARSET=utf8;
76
77
78-- -----------------------------------------------------
79-- Table `mydb`.`Contact_Details`
80-- -----------------------------------------------------
81DROP TABLE IF EXISTS `mydb`.`Contact_Details` ;
82
83CREATE TABLE IF NOT EXISTS `mydb`.`Contact_Details` (
84 `idContact_Details` VARCHAR(20) NOT NULL,
85 `Salutation` VARCHAR(70) NULL,
86 `First_Name` VARCHAR(30) NULL,
87 `Last_Name` VARCHAR(50) NULL,
88 `Title` VARCHAR(130) NULL,
89 `Mailing_Street` VARCHAR(175) NULL,
90 `Mailing_City` VARCHAR(50) NULL,
91 `Mailing_StateProvince` VARCHAR(30) NULL,
92 `Mailing_PostalCode` VARCHAR(20) NULL,
93 `Mailing_Country` VARCHAR(25) NULL,
94 `Phone` VARCHAR(50) NULL,
95 `Mobile` VARCHAR(35) NULL,
96 `Fax` VARCHAR(45) NULL,
97 `Email` VARCHAR(75) NULL,
98 `Email_OptOut` VARCHAR(1) NULL,
99 `Account_Owner` VARCHAR(20) NULL,
100 `Account_Name` VARCHAR(125) NULL,
101 `Account_ID` VARCHAR(20) NOT NULL,
102 `Active` VARCHAR(2) NOT NULL,
103 PRIMARY KEY (`idContact_Details`),
104 UNIQUE INDEX `idContact_Details_UNIQUE` (`idContact_Details` ASC))
105ENGINE = InnoDB DEFAULT CHARSET=utf8;
106
107'INDEX `Account_ID_idx` (`Account_ID` ASC)'
108
109-- -----------------------------------------------------
110-- Table `mydb`.`WF1_SR_Accounts`
111-- -----------------------------------------------------
112DROP TABLE IF EXISTS `mydb`.`WF1_SR_Accounts` ;
113
114CREATE TABLE IF NOT EXISTS `mydb`.`WF1_SR_Accounts` (
115 `idWF1_SR_Accounts` VARCHAR(20) NOT NULL,
116 `Name` VARCHAR(125) NOT NULL,
117 `Billing_Street` VARCHAR(135) NULL,
118 `Billing_City` VARCHAR(50) NULL,
119 `Billing_State` VARCHAR(20) NULL,
120 `Billing_PostalCode` VARCHAR(20) NULL,
121 `Billing_Country` VARCHAR(25) NULL,
122 `Location_Type` VARCHAR(20) NULL,
123 `Parent_ID` VARCHAR(20) NULL,
124 `Parent_Name` VARCHAR(125) NULL,
125 `SR_Account?` VARCHAR(5) NULL,
126 `WF1_Account?` VARCHAR(5) NULL,
127 `WF1_AccountNum` VARCHAR(10) NULL,
128 `WF1_AccountStatus` VARCHAR(25) NULL,
129 `WF1_AccountCreated` VARCHAR(50) NULL,
130 `WF1_BU` VARCHAR(10) NULL,
131 `WF1_Collector` VARCHAR(20) NULL,
132 `WF1_CreditHold` VARCHAR(5) NULL,
133 `WF1_CreditLimit` VARCHAR(10) NULL,
134 `WF1_CreditRating` VARCHAR(15) NULL,
135 `WF1_DVP` VARCHAR(20) NULL,
136 `WF1_PaymentTerms` VARCHAR(20) NULL,
137 `WF1_RSD` VARCHAR(40) NULL,
138 `WF1_TerritoryName` VARCHAR(45) NULL,
139 `WF1_TerritoryNumber` VARCHAR(5) NULL,
140 `Select_` VARCHAR(5) NULL,
141 `New_Select` VARCHAR(5) NULL,
142 `CRMFusion_Use` VARCHAR(10) NULL,
143 `CRMFusion_Change` VARCHAR(10) NULL,
144 PRIMARY KEY (`idWF1_SR_Accounts`),
145 UNIQUE INDEX `idWF1_SR_Accounts_UNIQUE` (`idWF1_SR_Accounts` ASC))
146ENGINE = InnoDB DEFAULT CHARSET=utf8;
147
148
149SET SQL_MODE=@OLD_SQL_MODE;
150SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
151SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
152
153SHOW VARIABLES like 'char%';
154
155Variable_name Value
156character_set_client latin1
157character_set_connection latin1
158character_set_database utf8
159character_set_filesystem binary
160character_set_results latin1
161character_set_server latin1
162character_set_system utf8
163character_sets_dir C:Program FilesMySQLMySQL Server 5.5sharecharsets