10:34:02 [ERR][ grt]: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') VISIBLE) ENGINE = InnoDB' at line 15
Posted by: Haywood Parker
Date: September 29, 2023 09:50AM
Date: September 29, 2023 09:50AM
Please Help this newbie.
I created a EER Diagram of our business model in MySql Workbench.
The Database Schema has all of the tables relationships and datatypes in it that I want to use in my AWS RDS Database Instance.
I have an AWS account so I used RDS to create a database BTCBBF5.
I was able to connect to the database with MySql Workbench it tests successfully.
When I attempt to use Workbench to Forward Engineer the EER Diagram Schema into the AWS RDS Database Instance BTCBBF5 I receive this error.
10:34:02 [ERR][ grt]: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') VISIBLE)
ENGINE = InnoDB' at line 15
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;
-- -----------------------------------------------------
-- Table `mydb`.`Finance`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Finance` (
`Location_Num` INT(3) UNSIGNED NOT NULL,
`Tran_ID` INT(11) UNSIGNED NOT NULL,
`Subject` VARCHAR(100) NOT NULL,
`Amount` DECIMAL(7,2) NOT NULL,
`Currency` CHAR(3) NOT NULL,
`Direction` INT(11) NOT NULL,
`Payroll` DECIMAL(7,2) NOT NULL,
`Taxes` DECIMAL(7,2) NOT NULL,
`Insurance` DECIMAL(7,2) NOT NULL,
PRIMARY KEY (`Location_Num`),
INDEX `Tran_ID` () VISIBLE,
UNIQUE INDEX `Tran_ID_UNIQUE` (`Tran_ID` ASC) VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Location` (
`Location_Name` VARCHAR(45) NOT NULL,
`Location_City` VARCHAR(45) NOT NULL,
`Location_State` VARCHAR(45) NOT NULL,
`Location Zip` INT(5) UNSIGNED NOT NULL,
`Location_Phone` VARCHAR(10) NOT NULL,
`Location_Rent` DECIMAL(7,2) NOT NULL,
`Location_Water` DECIMAL(7,2) NOT NULL,
`Location_Lights` DECIMAL(7,2) NOT NULL,
`Location_Gas` DECIMAL(7,2) NOT NULL,
`Location_Internet` DECIMAL(7,2) NOT NULL,
`Location_Phone` DECIMAL(7,2) UNSIGNED NOT NULL,
`Location_Manager` VARCHAR(45) NOT NULL,
`Finance_Location_Num` INT(3) UNSIGNED NOT NULL,
`Client_Num` INT(6) UNSIGNED NOT NULL,
INDEX `fk_Location_Finance1_idx` (`Finance_Location_Num` ASC) VISIBLE,
UNIQUE INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE,
CONSTRAINT `fk_Location_Finance1`
FOREIGN KEY (`Finance_Location_Num`)
REFERENCES `mydb`.`Finance` (`Location_Num`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Staff`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Staff` (
`Staff_ID_Num` INT(3) UNSIGNED NOT NULL,
`First_Name` VARCHAR(45) NOT NULL,
`Last_Name` VARCHAR(45) NOT NULL,
`Staff_Position` VARCHAR(45) NOT NULL,
`Department_ID` INT(3) UNSIGNED NOT NULL,
PRIMARY KEY (`Staff_ID_Num`),
UNIQUE INDEX `Department_ID_UNIQUE` (`Department_ID` ASC) VISIBLE,
UNIQUE INDEX `Staff_ID_Num_UNIQUE` (`Staff_ID_Num` ASC) VISIBLE,
INDEX `First_Name` () VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Services`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Services` (
`Client_Num` INT(6) UNSIGNED NOT NULL,
`Job` VARCHAR(45) NOT NULL DEFAULT 'No',
`Training` VARCHAR(45) NOT NULL DEFAULT 'No',
`Resume` VARCHAR(45) NOT NULL DEFAULT 'No',
`US_Visa` VARCHAR(45) NOT NULL DEFAULT 'No',
`Community_Hours` VARCHAR(45) NOT NULL DEFAULT 'No',
`Record_Expunged` VARCHAR(45) NOT NULL DEFAULT 'No',
`Food` VARCHAR(45) NOT NULL DEFAULT 'No',
`Clothing` VARCHAR(45) NOT NULL DEFAULT 'No',
`Haircut` VARCHAR(45) NOT NULL DEFAULT 'No',
`Housing` VARCHAR(45) NOT NULL DEFAULT 'No',
`Spiritual_Guidance` VARCHAR(45) NOT NULL DEFAULT 'No',
`Medical_Care` VARCHAR(45) NOT NULL DEFAULT 'No',
`CareGiver` VARCHAR(45) NOT NULL DEFAULT 'No',
`Assessment_Date` DATETIME NOT NULL,
`Staff_ID_Num` INT(3) UNSIGNED NOT NULL,
PRIMARY KEY (`Client_Num`),
INDEX `Staff_Id_num_idx` (`Staff_ID_Num` ASC) VISIBLE,
UNIQUE INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE,
UNIQUE INDEX `Staff_ID_Num_UNIQUE` (`Staff_ID_Num` ASC) VISIBLE,
CONSTRAINT `Staff_Id_num`
FOREIGN KEY (`Staff_ID_Num`)
REFERENCES `mydb`.`Staff` (`Staff_ID_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Renters`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Renters` (
`Renters_Id` INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
`Client_Num` INT(6) UNSIGNED NOT NULL,
`Rental_Date` DATETIME NOT NULL,
`Rental_2_Forms_of_Id` VARCHAR(45) NOT NULL DEFAULT 'No',
`Property_Id_Num` INT(6) UNSIGNED NOT NULL,
`Rental_Amount` DECIMAL(2) NOT NULL,
`Start_Of_Service_Date` DATETIME NOT NULL,
`Rental_Area_Of_Interest` VARCHAR(45) NOT NULL,
`Rental_Range_Accepted` DECIMAL(2) NOT NULL,
`Rental_Size_Needed` VARCHAR(45) NOT NULL,
`Rental_8AVoucher_Approved` VARCHAR(45) NOT NULL DEFAULT 'No',
`Rental_Deposit_Required` DECIMAL(2) NOT NULL,
`Rental_Proof_Of_Income` VARCHAR(45) NOT NULL,
`Rental_Background_Check_Complete` VARCHAR(45) NOT NULL DEFAULT 'No',
`Property_Id_Num` INT(6) UNSIGNED NOT NULL,
PRIMARY KEY (`Renters_Id`),
UNIQUE INDEX `Property_Id_Num_UNIQUE` (`Property_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Renters_Id_UNIQUE` (`Renters_Id` ASC) VISIBLE,
UNIQUE INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE,
UNIQUE INDEX `Property_Id_Num_UNIQUE` (`Property_Id_Num` ASC) VISIBLE,
INDEX `Rental_date` () VISIBLE,
INDEX `Start_Of_Service_date` () VISIBLE,
INDEX `Rental_Size_Needed` () VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Real_Estate_Source`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Real_Estate_Source` (
`Property_Id_Num` INT(6) NOT NULL,
`Property_Street_Address` VARCHAR(45) NOT NULL,
`Property_City` VARCHAR(45) NOT NULL,
`Property_State` VARCHAR(2) NOT NULL,
`Property_Zip` INT(5) NOT NULL,
`Property_Foreclosure` VARCHAR(45) NOT NULL,
`Property_Title_Company` VARCHAR(45) NOT NULL,
`Property_Bank_Holds_Title` VARCHAR(45) NOT NULL,
`Property_Posession_Amount` DECIMAL NOT NULL,
`Property_Posession_Date` DATETIME NOT NULL,
`Client_Num` INT(6) NOT NULL,
`Renters_Renters_Id` INT(6) UNSIGNED NOT NULL,
PRIMARY KEY (`Property_Id_Num`),
INDEX `fk_Real_Estate_Source_Renters1_idx` (`Renters_Renters_Id` ASC) VISIBLE,
INDEX `Property_State` () VISIBLE,
INDEX `Property_foreclosure` () VISIBLE,
INDEX `Property_Bank_Holds_Title` () VISIBLE,
CONSTRAINT `Client_Num`
FOREIGN KEY (`Client_Num`)
REFERENCES `mydb`.`Clients` (`Client_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Real_Estate_Source_Renters1`
FOREIGN KEY (`Renters_Renters_Id`)
REFERENCES `mydb`.`Renters` (`Renters_Id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`DWS_Emplyees`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`DWS_Emplyees` (
`Employee_Id_Num` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`Employee_Start_Date` DATE NULL,
`Employee_Part_time` VARCHAR(3) NOT NULL DEFAULT 'No',
`Employee_Fulll_Time` VARCHAR(3) NOT NULL DEFAULT 'No',
`Employee_Job_Title` VARCHAR(45) NOT NULL,
`Employee_Job_Description` MEDIUMBLOB NOT NULL,
PRIMARY KEY (`Employee_Id_Num`),
UNIQUE INDEX `Employee_Id_Num_UNIQUE` (`Employee_Id_Num` ASC) VISIBLE,
INDEX `Employee_Start_Date` () VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`DWS_Staffing_Contracts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`DWS_Staffing_Contracts` (
`Staff_Contract_Id_Num` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`Staff_Contract_Name` VARCHAR(80) NOT NULL,
`Staff_Contract_Address` VARCHAR(45) NOT NULL,
`Staff_Contract_City` VARCHAR(45) NOT NULL,
`Staff_Contract_St` VARCHAR(45) NOT NULL,
`Staff_Contract_Zip` INT(5) UNSIGNED NOT NULL,
`Staff_Contract_Phone` INT(10) UNSIGNED NOT NULL,
`Staff_Contract_Total_Hours` VARCHAR(45) NOT NULL,
`Staff_Contract_Total_Hours_Worked` VARCHAR(45) NOT NULL,
`Employee_Id_Num` INT(5) UNSIGNED NOT NULL,
PRIMARY KEY (`Staff_Contract_Id_Num`),
UNIQUE INDEX `Employee_ID_Num_UNIQUE` (`Staff_Contract_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Employee_Id_Num_UNIQUE` (`Employee_Id_Num` ASC) VISIBLE,
INDEX `Staff_Contract_Name` () VISIBLE,
INDEX `Staff_Contract_City` () VISIBLE,
INDEX `Staff_Contract_St` () VISIBLE,
INDEX `Staff_Contract_Zip` () VISIBLE,
CONSTRAINT `Employee_Id_Num`
FOREIGN KEY ()
REFERENCES `mydb`.`DWS_Emplyees` ()
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`BTCBBF_Social`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`BTCBBF_Social` (
`Engagement_Id_Num` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`Immigration_Services` VARCHAR(45) NULL,
`Immigration_Visa_Status` VARCHAR(45) NULL,
`Immigration_Visa` VARCHAR(45) NULL,
`Food_Services_Refferal` VARCHAR(45) NULL,
`Mental_Health_Refferal` VARCHAR(45) NULL,
`Veteran_Services` VARCHAR(45) NULL,
`Community_Hour_Services` VARCHAR(45) NULL,
`Department_ID` INT(3) UNSIGNED NOT NULL,
PRIMARY KEY (`Engagement_Id_Num`),
UNIQUE INDEX `Engagement_Id_Num_UNIQUE` (`Engagement_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Department_ID_UNIQUE` (`Department_ID` ASC) VISIBLE,
INDEX `Immigration_Visa` () VISIBLE,
INDEX `Veteran_Services` () VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Departments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Departments` (
`Department_ID` INT(3) UNSIGNED NOT NULL,
`BTCBBF_Social` VARCHAR(45) NOT NULL DEFAULT 'No',
`DWS_Staffing` VARCHAR(45) NOT NULL DEFAULT 'No',
`WPTC_Training` VARCHAR(45) NOT NULL DEFAULT 'No',
`BTCBBF_Housing` VARCHAR(45) NOT NULL,
`Client_Num` INT(6) UNSIGNED NOT NULL,
`DWS_Staffing_Employee_ID_Num` INT(5) UNSIGNED NOT NULL,
`Department_ID` INT(3) UNSIGNED NOT NULL,
PRIMARY KEY (`Department_ID`),
INDEX `Client_Num_idx` (`Client_Num` ASC) VISIBLE,
INDEX `fk_Departments_DWS_Staffing1_idx` (`DWS_Staffing_Employee_ID_Num` ASC) VISIBLE,
UNIQUE INDEX `Department_ID_UNIQUE` (`Department_ID` ASC) VISIBLE,
UNIQUE INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE,
UNIQUE INDEX `Department_ID_UNIQUE` (`Department_ID` ASC) VISIBLE,
UNIQUE INDEX `DWS_Staffing_Employee_ID_Num_UNIQUE` (`DWS_Staffing_Employee_ID_Num` ASC) VISIBLE,
CONSTRAINT `Department_ID`
FOREIGN KEY (`Department_ID`)
REFERENCES `mydb`.`Staff` (`Staff_ID_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Client_Num`
FOREIGN KEY (`Client_Num`)
REFERENCES `mydb`.`Real_Estate_Source` (`Property_Id_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Departments_DWS_Staffing1`
FOREIGN KEY (`DWS_Staffing_Employee_ID_Num`)
REFERENCES `mydb`.`DWS_Staffing_Contracts` (`Staff_Contract_Id_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Department_ID`
FOREIGN KEY (`Department_ID`)
REFERENCES `mydb`.`BTCBBF_Social` (`Department_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`WPTC_Training`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`WPTC_Training` (
`Departnet_ID` INT(3) NOT NULL,
`Student_Id_Num` INT(5) UNSIGNED NOT NULL,
`Student_First_Name` VARCHAR(45) NOT NULL,
`Student_Last_Name` VARCHAR(45) NOT NULL,
`Client_Num` INT(6) UNSIGNED NOT NULL,
PRIMARY KEY (`Departnet_ID`),
UNIQUE INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE,
UNIQUE INDEX `Student_Id_Num_UNIQUE` (`Student_Id_Num` ASC) VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Contacts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Contacts` (
`First_Name` VARCHAR(100) NOT NULL,
`Last_Name` VARCHAR(60) NOT NULL,
`Middle_Init` VARCHAR(3) NOT NULL,
`Email_Address` VARCHAR(255) NOT NULL,
`Client_City` VARCHAR(45) NOT NULL,
`Client_St` VARCHAR(4) NOT NULL,
`Client_Zip` INT(5) UNSIGNED NOT NULL,
`Client_Num` INT(6) UNSIGNED NOT NULL,
`Client_Home_Phone` INT(10) UNSIGNED NOT NULL,
`Client_Cell_Phone` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`First_Name`),
INDEX `Email_Address_UNIQUE` (`Email_Address` ASC) VISIBLE,
INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE,
INDEX `Client_First_Name` () VISIBLE,
INDEX `ClientLast_Name` () VISIBLE,
INDEX `Client_City` () VISIBLE,
INDEX `Client_Zip` () VISIBLE,
UNIQUE INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Clients`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Clients` (
`Client_Num` INT(6) NOT NULL AUTO_INCREMENT,
`Location_Num` INT(3) UNSIGNED NOT NULL,
`Client_Date` DATETIME NOT NULL,
`Client_Drivers_Lic_St` VARCHAR(4) NOT NULL,
`Client_Drivers_Lic_Num` INT(16) UNSIGNED NOT NULL,
`Clients_Drivers_Lic_ExpDate` DATE NOT NULL,
`Clients_SSN` INT(9) NOT NULL,
`Clients_Photo` BLOB NOT NULL,
`Client_Email` VARCHAR(255) NOT NULL,
`Client_DOB` TIMESTAMP(19) NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Client_Record_Created` TIMESTAMP(19) NOT NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`Clients_Client_Num` INT(6) UNSIGNED NOT NULL,
`Contacts_First_Name` VARCHAR(100) NOT NULL,
PRIMARY KEY (`Client_Num`, `Clients_Client_Num`, `Contacts_First_Name`),
INDEX `fk_Clients_Contacts1_idx` (`Contacts_First_Name` ASC) VISIBLE,
UNIQUE INDEX `Clients_Client_Num_UNIQUE` (`Clients_Client_Num` ASC) VISIBLE,
CONSTRAINT `Client_Num`
FOREIGN KEY (`Client_Num`)
REFERENCES `mydb`.`Services` (`Client_Num`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `Client_Num`
FOREIGN KEY (`Client_Num`)
REFERENCES `mydb`.`Departments` (`Department_ID`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `Client_Num`
FOREIGN KEY ()
REFERENCES `mydb`.`WPTC_Training` ()
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `Client_Num`
FOREIGN KEY (`Client_Num`)
REFERENCES `mydb`.`Location` (`Location Zip`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_Clients_Contacts1`
FOREIGN KEY (`Contacts_First_Name`)
REFERENCES `mydb`.`Contacts` (`First_Name`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Status`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Status` (
`Ex-Offender` BINARY(2) NOT NULL,
`Homeless` BINARY(2) NOT NULL,
`Veteran` BINARY(2) NOT NULL,
`Domestic_Violence` BINARY(2) NOT NULL,
`Imigrant` BINARY(2) NOT NULL,
`US_Citizen` BINARY(2) NOT NULL,
`Client_Num` INT(6) UNSIGNED NOT NULL,
`Clients_Client_Num` INT(6) UNSIGNED NOT NULL,
`Clients_Clients_Client_Num` INT(6) UNSIGNED NOT NULL,
`Clients_Contacts_First_Name` VARCHAR(100) NOT NULL,
UNIQUE INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE,
INDEX `fk_Status_Clients1_idx` (`Clients_Client_Num` ASC, `Clients_Clients_Client_Num` ASC, `Clients_Contacts_First_Name` ASC) VISIBLE,
UNIQUE INDEX `Clients_Client_Num_UNIQUE` (`Clients_Client_Num` ASC) VISIBLE,
UNIQUE INDEX `Clients_Clients_Client_Num_UNIQUE` (`Clients_Clients_Client_Num` ASC) VISIBLE,
CONSTRAINT `fk_Status_Clients1`
FOREIGN KEY (`Clients_Client_Num` , `Clients_Clients_Client_Num` , `Clients_Contacts_First_Name`)
REFERENCES `mydb`.`Clients` (`Client_Num` , `Clients_Client_Num` , `Contacts_First_Name`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Contractors_1099_List`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Contractors_1099_List` (
`Contractors_Id_Num` INT(6) UNSIGNED NOT NULL,
`Property_Id_Num` INT(6) UNSIGNED NOT NULL,
`Contractors_First_Name` VARCHAR(45) NOT NULL,
`Contractors_Last_Name` VARCHAR(45) NOT NULL,
`Contractors_Business_Name` VARCHAR(45) NOT NULL,
`Contractors_Business_Street_Address` VARCHAR(45) NOT NULL,
`Contractor_Licensed` VARCHAR(45) NOT NULL,
`Contractor_Bonded` VARCHAR(45) NOT NULL,
`Contractor_Insured` VARCHAR(45) NOT NULL,
`Contractors_Trade` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Contractors_Id_Num`),
UNIQUE INDEX `Contractors_Id_Num_UNIQUE` (`Contractors_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Property_Id_Num_UNIQUE` (`Property_Id_Num` ASC) VISIBLE,
INDEX `Contractor_Business_Name` () VISIBLE,
INDEX `Contractors_Last_Name` () VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Materials`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Materials` (
`Task_Id_Num` INT(6) UNSIGNED NOT NULL,
`Materials_List_Id_Num` INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
`Item_Name` VARCHAR(45) NOT NULL,
`Item_Description` VARCHAR(145) NOT NULL,
`Item_Price` DECIMAL(2) NOT NULL,
`Materials_Items_List_Total` DECIMAL(2) NOT NULL,
`Materials_Items_Purchase_Receipt` BLOB NOT NULL,
`Property_Id_Num` INT(6) UNSIGNED NOT NULL,
PRIMARY KEY (`Materials_List_Id_Num`),
UNIQUE INDEX `Property_Id_Num_UNIQUE` (`Property_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Materials_List_Id_Num_UNIQUE` (`Materials_List_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Task_Id_Num_UNIQUE` (`Task_Id_Num` ASC) VISIBLE,
INDEX `Item_Name` () VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Task`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Task` (
`Property_Id_Num` INT(6) UNSIGNED NOT NULL,
`Task_ID_Num` INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
`Task_Description` VARCHAR(155) NOT NULL,
`Task_Trade_Assigned` VARCHAR(45) NOT NULL,
`Task_Contractor_Assigned` VARCHAR(45) NOT NULL,
`Task_Status` VARCHAR(45) NOT NULL,
`Task_Contractor_Signature` VARCHAR(45) NOT NULL,
`Task_Prior_Approval_Required` VARCHAR(45) NOT NULL,
`Task_Materials_List` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Task_ID_Num`),
INDEX `Property_Id_Num_idx` (`Property_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Property_Id_Num_UNIQUE` (`Property_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Task_ID_Num_UNIQUE` (`Task_ID_Num` ASC) VISIBLE,
INDEX `Task_Contractor_Assigned` () VISIBLE,
INDEX `Task_Trade_Assigned` () VISIBLE,
INDEX `Task_Status` () VISIBLE,
CONSTRAINT `Property_Id_Num`
FOREIGN KEY (`Property_Id_Num`)
REFERENCES `mydb`.`Materials` (`Task_Id_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Property_Rehab`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Property_Rehab` (
`Property_Id_Num` INT(6) UNSIGNED NOT NULL,
`Client_Num` INT(6) UNSIGNED NOT NULL,
`Property_Home_Inspection` VARCHAR(45) NOT NULL DEFAULT 'No',
`Property_Statement_of_Work` VARCHAR(45) NOT NULL,
`Property_Rehab_Permits` VARCHAR(45) NOT NULL,
`Property_Water_Utility_Name` VARCHAR(45) NOT NULL,
`Property_Gas_Utility_Name` VARCHAR(45) NOT NULL,
`Property_Elecctric_Utility_Name` VARCHAR(45) NOT NULL,
`Property_Real_Estate_Taxes_Paid` VARCHAR(45) NOT NULL,
`Property_Home_Owners_Insurance` VARCHAR(45) NOT NULL,
`Contractor_ID_Num` INT(6) UNSIGNED NOT NULL,
`Task_ID_Num` INT(6) UNSIGNED NOT NULL,
PRIMARY KEY (`Property_Id_Num`),
INDEX `Client_Num_idx` (`Client_Num` ASC) VISIBLE,
INDEX `Contractor_ID_Num_idx` (`Contractor_ID_Num` ASC) VISIBLE,
INDEX `Task_ID_Num_idx` (`Task_ID_Num` ASC) VISIBLE,
UNIQUE INDEX `Property_Id_Num_UNIQUE` (`Property_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE,
UNIQUE INDEX `Contractor_ID_Num_UNIQUE` (`Contractor_ID_Num` ASC) VISIBLE,
UNIQUE INDEX `Task_ID_Num_UNIQUE` (`Task_ID_Num` ASC) VISIBLE,
CONSTRAINT `Property_ID_Num`
FOREIGN KEY (`Property_Id_Num`)
REFERENCES `mydb`.`Real_Estate_Source` (`Property_Id_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Client_Num`
FOREIGN KEY (`Client_Num`)
REFERENCES `mydb`.`Clients` (`Client_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Contractor_ID_Num`
FOREIGN KEY (`Contractor_ID_Num`)
REFERENCES `mydb`.`Contractors_1099_List` (`Contractors_Id_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Task_ID_Num`
FOREIGN KEY (`Task_ID_Num`)
REFERENCES `mydb`.`Task` (`Property_Id_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
I created a EER Diagram of our business model in MySql Workbench.
The Database Schema has all of the tables relationships and datatypes in it that I want to use in my AWS RDS Database Instance.
I have an AWS account so I used RDS to create a database BTCBBF5.
I was able to connect to the database with MySql Workbench it tests successfully.
When I attempt to use Workbench to Forward Engineer the EER Diagram Schema into the AWS RDS Database Instance BTCBBF5 I receive this error.
10:34:02 [ERR][ grt]: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') VISIBLE)
ENGINE = InnoDB' at line 15
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;
-- -----------------------------------------------------
-- Table `mydb`.`Finance`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Finance` (
`Location_Num` INT(3) UNSIGNED NOT NULL,
`Tran_ID` INT(11) UNSIGNED NOT NULL,
`Subject` VARCHAR(100) NOT NULL,
`Amount` DECIMAL(7,2) NOT NULL,
`Currency` CHAR(3) NOT NULL,
`Direction` INT(11) NOT NULL,
`Payroll` DECIMAL(7,2) NOT NULL,
`Taxes` DECIMAL(7,2) NOT NULL,
`Insurance` DECIMAL(7,2) NOT NULL,
PRIMARY KEY (`Location_Num`),
INDEX `Tran_ID` () VISIBLE,
UNIQUE INDEX `Tran_ID_UNIQUE` (`Tran_ID` ASC) VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Location` (
`Location_Name` VARCHAR(45) NOT NULL,
`Location_City` VARCHAR(45) NOT NULL,
`Location_State` VARCHAR(45) NOT NULL,
`Location Zip` INT(5) UNSIGNED NOT NULL,
`Location_Phone` VARCHAR(10) NOT NULL,
`Location_Rent` DECIMAL(7,2) NOT NULL,
`Location_Water` DECIMAL(7,2) NOT NULL,
`Location_Lights` DECIMAL(7,2) NOT NULL,
`Location_Gas` DECIMAL(7,2) NOT NULL,
`Location_Internet` DECIMAL(7,2) NOT NULL,
`Location_Phone` DECIMAL(7,2) UNSIGNED NOT NULL,
`Location_Manager` VARCHAR(45) NOT NULL,
`Finance_Location_Num` INT(3) UNSIGNED NOT NULL,
`Client_Num` INT(6) UNSIGNED NOT NULL,
INDEX `fk_Location_Finance1_idx` (`Finance_Location_Num` ASC) VISIBLE,
UNIQUE INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE,
CONSTRAINT `fk_Location_Finance1`
FOREIGN KEY (`Finance_Location_Num`)
REFERENCES `mydb`.`Finance` (`Location_Num`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Staff`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Staff` (
`Staff_ID_Num` INT(3) UNSIGNED NOT NULL,
`First_Name` VARCHAR(45) NOT NULL,
`Last_Name` VARCHAR(45) NOT NULL,
`Staff_Position` VARCHAR(45) NOT NULL,
`Department_ID` INT(3) UNSIGNED NOT NULL,
PRIMARY KEY (`Staff_ID_Num`),
UNIQUE INDEX `Department_ID_UNIQUE` (`Department_ID` ASC) VISIBLE,
UNIQUE INDEX `Staff_ID_Num_UNIQUE` (`Staff_ID_Num` ASC) VISIBLE,
INDEX `First_Name` () VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Services`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Services` (
`Client_Num` INT(6) UNSIGNED NOT NULL,
`Job` VARCHAR(45) NOT NULL DEFAULT 'No',
`Training` VARCHAR(45) NOT NULL DEFAULT 'No',
`Resume` VARCHAR(45) NOT NULL DEFAULT 'No',
`US_Visa` VARCHAR(45) NOT NULL DEFAULT 'No',
`Community_Hours` VARCHAR(45) NOT NULL DEFAULT 'No',
`Record_Expunged` VARCHAR(45) NOT NULL DEFAULT 'No',
`Food` VARCHAR(45) NOT NULL DEFAULT 'No',
`Clothing` VARCHAR(45) NOT NULL DEFAULT 'No',
`Haircut` VARCHAR(45) NOT NULL DEFAULT 'No',
`Housing` VARCHAR(45) NOT NULL DEFAULT 'No',
`Spiritual_Guidance` VARCHAR(45) NOT NULL DEFAULT 'No',
`Medical_Care` VARCHAR(45) NOT NULL DEFAULT 'No',
`CareGiver` VARCHAR(45) NOT NULL DEFAULT 'No',
`Assessment_Date` DATETIME NOT NULL,
`Staff_ID_Num` INT(3) UNSIGNED NOT NULL,
PRIMARY KEY (`Client_Num`),
INDEX `Staff_Id_num_idx` (`Staff_ID_Num` ASC) VISIBLE,
UNIQUE INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE,
UNIQUE INDEX `Staff_ID_Num_UNIQUE` (`Staff_ID_Num` ASC) VISIBLE,
CONSTRAINT `Staff_Id_num`
FOREIGN KEY (`Staff_ID_Num`)
REFERENCES `mydb`.`Staff` (`Staff_ID_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Renters`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Renters` (
`Renters_Id` INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
`Client_Num` INT(6) UNSIGNED NOT NULL,
`Rental_Date` DATETIME NOT NULL,
`Rental_2_Forms_of_Id` VARCHAR(45) NOT NULL DEFAULT 'No',
`Property_Id_Num` INT(6) UNSIGNED NOT NULL,
`Rental_Amount` DECIMAL(2) NOT NULL,
`Start_Of_Service_Date` DATETIME NOT NULL,
`Rental_Area_Of_Interest` VARCHAR(45) NOT NULL,
`Rental_Range_Accepted` DECIMAL(2) NOT NULL,
`Rental_Size_Needed` VARCHAR(45) NOT NULL,
`Rental_8AVoucher_Approved` VARCHAR(45) NOT NULL DEFAULT 'No',
`Rental_Deposit_Required` DECIMAL(2) NOT NULL,
`Rental_Proof_Of_Income` VARCHAR(45) NOT NULL,
`Rental_Background_Check_Complete` VARCHAR(45) NOT NULL DEFAULT 'No',
`Property_Id_Num` INT(6) UNSIGNED NOT NULL,
PRIMARY KEY (`Renters_Id`),
UNIQUE INDEX `Property_Id_Num_UNIQUE` (`Property_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Renters_Id_UNIQUE` (`Renters_Id` ASC) VISIBLE,
UNIQUE INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE,
UNIQUE INDEX `Property_Id_Num_UNIQUE` (`Property_Id_Num` ASC) VISIBLE,
INDEX `Rental_date` () VISIBLE,
INDEX `Start_Of_Service_date` () VISIBLE,
INDEX `Rental_Size_Needed` () VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Real_Estate_Source`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Real_Estate_Source` (
`Property_Id_Num` INT(6) NOT NULL,
`Property_Street_Address` VARCHAR(45) NOT NULL,
`Property_City` VARCHAR(45) NOT NULL,
`Property_State` VARCHAR(2) NOT NULL,
`Property_Zip` INT(5) NOT NULL,
`Property_Foreclosure` VARCHAR(45) NOT NULL,
`Property_Title_Company` VARCHAR(45) NOT NULL,
`Property_Bank_Holds_Title` VARCHAR(45) NOT NULL,
`Property_Posession_Amount` DECIMAL NOT NULL,
`Property_Posession_Date` DATETIME NOT NULL,
`Client_Num` INT(6) NOT NULL,
`Renters_Renters_Id` INT(6) UNSIGNED NOT NULL,
PRIMARY KEY (`Property_Id_Num`),
INDEX `fk_Real_Estate_Source_Renters1_idx` (`Renters_Renters_Id` ASC) VISIBLE,
INDEX `Property_State` () VISIBLE,
INDEX `Property_foreclosure` () VISIBLE,
INDEX `Property_Bank_Holds_Title` () VISIBLE,
CONSTRAINT `Client_Num`
FOREIGN KEY (`Client_Num`)
REFERENCES `mydb`.`Clients` (`Client_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Real_Estate_Source_Renters1`
FOREIGN KEY (`Renters_Renters_Id`)
REFERENCES `mydb`.`Renters` (`Renters_Id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`DWS_Emplyees`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`DWS_Emplyees` (
`Employee_Id_Num` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`Employee_Start_Date` DATE NULL,
`Employee_Part_time` VARCHAR(3) NOT NULL DEFAULT 'No',
`Employee_Fulll_Time` VARCHAR(3) NOT NULL DEFAULT 'No',
`Employee_Job_Title` VARCHAR(45) NOT NULL,
`Employee_Job_Description` MEDIUMBLOB NOT NULL,
PRIMARY KEY (`Employee_Id_Num`),
UNIQUE INDEX `Employee_Id_Num_UNIQUE` (`Employee_Id_Num` ASC) VISIBLE,
INDEX `Employee_Start_Date` () VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`DWS_Staffing_Contracts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`DWS_Staffing_Contracts` (
`Staff_Contract_Id_Num` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`Staff_Contract_Name` VARCHAR(80) NOT NULL,
`Staff_Contract_Address` VARCHAR(45) NOT NULL,
`Staff_Contract_City` VARCHAR(45) NOT NULL,
`Staff_Contract_St` VARCHAR(45) NOT NULL,
`Staff_Contract_Zip` INT(5) UNSIGNED NOT NULL,
`Staff_Contract_Phone` INT(10) UNSIGNED NOT NULL,
`Staff_Contract_Total_Hours` VARCHAR(45) NOT NULL,
`Staff_Contract_Total_Hours_Worked` VARCHAR(45) NOT NULL,
`Employee_Id_Num` INT(5) UNSIGNED NOT NULL,
PRIMARY KEY (`Staff_Contract_Id_Num`),
UNIQUE INDEX `Employee_ID_Num_UNIQUE` (`Staff_Contract_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Employee_Id_Num_UNIQUE` (`Employee_Id_Num` ASC) VISIBLE,
INDEX `Staff_Contract_Name` () VISIBLE,
INDEX `Staff_Contract_City` () VISIBLE,
INDEX `Staff_Contract_St` () VISIBLE,
INDEX `Staff_Contract_Zip` () VISIBLE,
CONSTRAINT `Employee_Id_Num`
FOREIGN KEY ()
REFERENCES `mydb`.`DWS_Emplyees` ()
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`BTCBBF_Social`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`BTCBBF_Social` (
`Engagement_Id_Num` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`Immigration_Services` VARCHAR(45) NULL,
`Immigration_Visa_Status` VARCHAR(45) NULL,
`Immigration_Visa` VARCHAR(45) NULL,
`Food_Services_Refferal` VARCHAR(45) NULL,
`Mental_Health_Refferal` VARCHAR(45) NULL,
`Veteran_Services` VARCHAR(45) NULL,
`Community_Hour_Services` VARCHAR(45) NULL,
`Department_ID` INT(3) UNSIGNED NOT NULL,
PRIMARY KEY (`Engagement_Id_Num`),
UNIQUE INDEX `Engagement_Id_Num_UNIQUE` (`Engagement_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Department_ID_UNIQUE` (`Department_ID` ASC) VISIBLE,
INDEX `Immigration_Visa` () VISIBLE,
INDEX `Veteran_Services` () VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Departments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Departments` (
`Department_ID` INT(3) UNSIGNED NOT NULL,
`BTCBBF_Social` VARCHAR(45) NOT NULL DEFAULT 'No',
`DWS_Staffing` VARCHAR(45) NOT NULL DEFAULT 'No',
`WPTC_Training` VARCHAR(45) NOT NULL DEFAULT 'No',
`BTCBBF_Housing` VARCHAR(45) NOT NULL,
`Client_Num` INT(6) UNSIGNED NOT NULL,
`DWS_Staffing_Employee_ID_Num` INT(5) UNSIGNED NOT NULL,
`Department_ID` INT(3) UNSIGNED NOT NULL,
PRIMARY KEY (`Department_ID`),
INDEX `Client_Num_idx` (`Client_Num` ASC) VISIBLE,
INDEX `fk_Departments_DWS_Staffing1_idx` (`DWS_Staffing_Employee_ID_Num` ASC) VISIBLE,
UNIQUE INDEX `Department_ID_UNIQUE` (`Department_ID` ASC) VISIBLE,
UNIQUE INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE,
UNIQUE INDEX `Department_ID_UNIQUE` (`Department_ID` ASC) VISIBLE,
UNIQUE INDEX `DWS_Staffing_Employee_ID_Num_UNIQUE` (`DWS_Staffing_Employee_ID_Num` ASC) VISIBLE,
CONSTRAINT `Department_ID`
FOREIGN KEY (`Department_ID`)
REFERENCES `mydb`.`Staff` (`Staff_ID_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Client_Num`
FOREIGN KEY (`Client_Num`)
REFERENCES `mydb`.`Real_Estate_Source` (`Property_Id_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Departments_DWS_Staffing1`
FOREIGN KEY (`DWS_Staffing_Employee_ID_Num`)
REFERENCES `mydb`.`DWS_Staffing_Contracts` (`Staff_Contract_Id_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Department_ID`
FOREIGN KEY (`Department_ID`)
REFERENCES `mydb`.`BTCBBF_Social` (`Department_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`WPTC_Training`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`WPTC_Training` (
`Departnet_ID` INT(3) NOT NULL,
`Student_Id_Num` INT(5) UNSIGNED NOT NULL,
`Student_First_Name` VARCHAR(45) NOT NULL,
`Student_Last_Name` VARCHAR(45) NOT NULL,
`Client_Num` INT(6) UNSIGNED NOT NULL,
PRIMARY KEY (`Departnet_ID`),
UNIQUE INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE,
UNIQUE INDEX `Student_Id_Num_UNIQUE` (`Student_Id_Num` ASC) VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Contacts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Contacts` (
`First_Name` VARCHAR(100) NOT NULL,
`Last_Name` VARCHAR(60) NOT NULL,
`Middle_Init` VARCHAR(3) NOT NULL,
`Email_Address` VARCHAR(255) NOT NULL,
`Client_City` VARCHAR(45) NOT NULL,
`Client_St` VARCHAR(4) NOT NULL,
`Client_Zip` INT(5) UNSIGNED NOT NULL,
`Client_Num` INT(6) UNSIGNED NOT NULL,
`Client_Home_Phone` INT(10) UNSIGNED NOT NULL,
`Client_Cell_Phone` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`First_Name`),
INDEX `Email_Address_UNIQUE` (`Email_Address` ASC) VISIBLE,
INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE,
INDEX `Client_First_Name` () VISIBLE,
INDEX `ClientLast_Name` () VISIBLE,
INDEX `Client_City` () VISIBLE,
INDEX `Client_Zip` () VISIBLE,
UNIQUE INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Clients`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Clients` (
`Client_Num` INT(6) NOT NULL AUTO_INCREMENT,
`Location_Num` INT(3) UNSIGNED NOT NULL,
`Client_Date` DATETIME NOT NULL,
`Client_Drivers_Lic_St` VARCHAR(4) NOT NULL,
`Client_Drivers_Lic_Num` INT(16) UNSIGNED NOT NULL,
`Clients_Drivers_Lic_ExpDate` DATE NOT NULL,
`Clients_SSN` INT(9) NOT NULL,
`Clients_Photo` BLOB NOT NULL,
`Client_Email` VARCHAR(255) NOT NULL,
`Client_DOB` TIMESTAMP(19) NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Client_Record_Created` TIMESTAMP(19) NOT NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`Clients_Client_Num` INT(6) UNSIGNED NOT NULL,
`Contacts_First_Name` VARCHAR(100) NOT NULL,
PRIMARY KEY (`Client_Num`, `Clients_Client_Num`, `Contacts_First_Name`),
INDEX `fk_Clients_Contacts1_idx` (`Contacts_First_Name` ASC) VISIBLE,
UNIQUE INDEX `Clients_Client_Num_UNIQUE` (`Clients_Client_Num` ASC) VISIBLE,
CONSTRAINT `Client_Num`
FOREIGN KEY (`Client_Num`)
REFERENCES `mydb`.`Services` (`Client_Num`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `Client_Num`
FOREIGN KEY (`Client_Num`)
REFERENCES `mydb`.`Departments` (`Department_ID`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `Client_Num`
FOREIGN KEY ()
REFERENCES `mydb`.`WPTC_Training` ()
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `Client_Num`
FOREIGN KEY (`Client_Num`)
REFERENCES `mydb`.`Location` (`Location Zip`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_Clients_Contacts1`
FOREIGN KEY (`Contacts_First_Name`)
REFERENCES `mydb`.`Contacts` (`First_Name`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Status`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Status` (
`Ex-Offender` BINARY(2) NOT NULL,
`Homeless` BINARY(2) NOT NULL,
`Veteran` BINARY(2) NOT NULL,
`Domestic_Violence` BINARY(2) NOT NULL,
`Imigrant` BINARY(2) NOT NULL,
`US_Citizen` BINARY(2) NOT NULL,
`Client_Num` INT(6) UNSIGNED NOT NULL,
`Clients_Client_Num` INT(6) UNSIGNED NOT NULL,
`Clients_Clients_Client_Num` INT(6) UNSIGNED NOT NULL,
`Clients_Contacts_First_Name` VARCHAR(100) NOT NULL,
UNIQUE INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE,
INDEX `fk_Status_Clients1_idx` (`Clients_Client_Num` ASC, `Clients_Clients_Client_Num` ASC, `Clients_Contacts_First_Name` ASC) VISIBLE,
UNIQUE INDEX `Clients_Client_Num_UNIQUE` (`Clients_Client_Num` ASC) VISIBLE,
UNIQUE INDEX `Clients_Clients_Client_Num_UNIQUE` (`Clients_Clients_Client_Num` ASC) VISIBLE,
CONSTRAINT `fk_Status_Clients1`
FOREIGN KEY (`Clients_Client_Num` , `Clients_Clients_Client_Num` , `Clients_Contacts_First_Name`)
REFERENCES `mydb`.`Clients` (`Client_Num` , `Clients_Client_Num` , `Contacts_First_Name`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Contractors_1099_List`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Contractors_1099_List` (
`Contractors_Id_Num` INT(6) UNSIGNED NOT NULL,
`Property_Id_Num` INT(6) UNSIGNED NOT NULL,
`Contractors_First_Name` VARCHAR(45) NOT NULL,
`Contractors_Last_Name` VARCHAR(45) NOT NULL,
`Contractors_Business_Name` VARCHAR(45) NOT NULL,
`Contractors_Business_Street_Address` VARCHAR(45) NOT NULL,
`Contractor_Licensed` VARCHAR(45) NOT NULL,
`Contractor_Bonded` VARCHAR(45) NOT NULL,
`Contractor_Insured` VARCHAR(45) NOT NULL,
`Contractors_Trade` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Contractors_Id_Num`),
UNIQUE INDEX `Contractors_Id_Num_UNIQUE` (`Contractors_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Property_Id_Num_UNIQUE` (`Property_Id_Num` ASC) VISIBLE,
INDEX `Contractor_Business_Name` () VISIBLE,
INDEX `Contractors_Last_Name` () VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Materials`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Materials` (
`Task_Id_Num` INT(6) UNSIGNED NOT NULL,
`Materials_List_Id_Num` INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
`Item_Name` VARCHAR(45) NOT NULL,
`Item_Description` VARCHAR(145) NOT NULL,
`Item_Price` DECIMAL(2) NOT NULL,
`Materials_Items_List_Total` DECIMAL(2) NOT NULL,
`Materials_Items_Purchase_Receipt` BLOB NOT NULL,
`Property_Id_Num` INT(6) UNSIGNED NOT NULL,
PRIMARY KEY (`Materials_List_Id_Num`),
UNIQUE INDEX `Property_Id_Num_UNIQUE` (`Property_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Materials_List_Id_Num_UNIQUE` (`Materials_List_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Task_Id_Num_UNIQUE` (`Task_Id_Num` ASC) VISIBLE,
INDEX `Item_Name` () VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Task`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Task` (
`Property_Id_Num` INT(6) UNSIGNED NOT NULL,
`Task_ID_Num` INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
`Task_Description` VARCHAR(155) NOT NULL,
`Task_Trade_Assigned` VARCHAR(45) NOT NULL,
`Task_Contractor_Assigned` VARCHAR(45) NOT NULL,
`Task_Status` VARCHAR(45) NOT NULL,
`Task_Contractor_Signature` VARCHAR(45) NOT NULL,
`Task_Prior_Approval_Required` VARCHAR(45) NOT NULL,
`Task_Materials_List` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Task_ID_Num`),
INDEX `Property_Id_Num_idx` (`Property_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Property_Id_Num_UNIQUE` (`Property_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Task_ID_Num_UNIQUE` (`Task_ID_Num` ASC) VISIBLE,
INDEX `Task_Contractor_Assigned` () VISIBLE,
INDEX `Task_Trade_Assigned` () VISIBLE,
INDEX `Task_Status` () VISIBLE,
CONSTRAINT `Property_Id_Num`
FOREIGN KEY (`Property_Id_Num`)
REFERENCES `mydb`.`Materials` (`Task_Id_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Property_Rehab`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Property_Rehab` (
`Property_Id_Num` INT(6) UNSIGNED NOT NULL,
`Client_Num` INT(6) UNSIGNED NOT NULL,
`Property_Home_Inspection` VARCHAR(45) NOT NULL DEFAULT 'No',
`Property_Statement_of_Work` VARCHAR(45) NOT NULL,
`Property_Rehab_Permits` VARCHAR(45) NOT NULL,
`Property_Water_Utility_Name` VARCHAR(45) NOT NULL,
`Property_Gas_Utility_Name` VARCHAR(45) NOT NULL,
`Property_Elecctric_Utility_Name` VARCHAR(45) NOT NULL,
`Property_Real_Estate_Taxes_Paid` VARCHAR(45) NOT NULL,
`Property_Home_Owners_Insurance` VARCHAR(45) NOT NULL,
`Contractor_ID_Num` INT(6) UNSIGNED NOT NULL,
`Task_ID_Num` INT(6) UNSIGNED NOT NULL,
PRIMARY KEY (`Property_Id_Num`),
INDEX `Client_Num_idx` (`Client_Num` ASC) VISIBLE,
INDEX `Contractor_ID_Num_idx` (`Contractor_ID_Num` ASC) VISIBLE,
INDEX `Task_ID_Num_idx` (`Task_ID_Num` ASC) VISIBLE,
UNIQUE INDEX `Property_Id_Num_UNIQUE` (`Property_Id_Num` ASC) VISIBLE,
UNIQUE INDEX `Client_Num_UNIQUE` (`Client_Num` ASC) VISIBLE,
UNIQUE INDEX `Contractor_ID_Num_UNIQUE` (`Contractor_ID_Num` ASC) VISIBLE,
UNIQUE INDEX `Task_ID_Num_UNIQUE` (`Task_ID_Num` ASC) VISIBLE,
CONSTRAINT `Property_ID_Num`
FOREIGN KEY (`Property_Id_Num`)
REFERENCES `mydb`.`Real_Estate_Source` (`Property_Id_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Client_Num`
FOREIGN KEY (`Client_Num`)
REFERENCES `mydb`.`Clients` (`Client_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Contractor_ID_Num`
FOREIGN KEY (`Contractor_ID_Num`)
REFERENCES `mydb`.`Contractors_1099_List` (`Contractors_Id_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Task_ID_Num`
FOREIGN KEY (`Task_ID_Num`)
REFERENCES `mydb`.`Task` (`Property_Id_Num`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Subject
Views
Written By
Posted
10:34:02 [ERR][ grt]: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') VISIBLE) ENGINE = InnoDB' at line 15
130
September 29, 2023 09:50AM
Sorry, only registered users may post in this forum.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.