I'm a novice trying my hand at creating a database for a friend's startup.
I created a database layout dbdesigner.net (diagram attached). I exported the SQL and have been trying repeatedly to import it into my phpmyadmin database using the sql tab.
Unfortunatley it keeps throwing up error after error the latest of which really has me stuck.
All help solving the issue and constructive feedback is greatly appreciated. Thanks in advance :)
The error is:
Static analysis:
1 errors were found during analysis.
Unrecognized data type. (near "AUTO_INCREMENT" at position 50)
SQL query:
CREATE TABLE `mtn_readings`( `mtn_readings_id` AUTO_INCREMENT, `mtn_readings_datetime` DATETIME NOT NULL, `cust_details_id` INT(5) NOT NULL, `mfd_id` INT(5) NOT NULL, `mtn_agreements_id` INT(5) NOT NULL, `mtn_readings_mono` VARCHAR(11) NOT NULL, `mtn_readings_colour` VARCHAR(11), `mtn_readings_type` VARCHAR(65) NOT NULL )
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AUTO_INCREMENT,
`mtn_readings_datetime` DATETIME NOT NULL,
`cust_details_i' at line 2
The sql query is:
CREATE TABLE `cust_details`(
`cust_details_id` INT(5) AUTO_INCREMENT,
`cust_details_name` VARCHAR(150) NOT NULL,
`cust_details_legal` VARCHAR(150) NOT NULL,
`cust_details_add1` VARCHAR(150) NOT NULL,
`cust_details_add2` VARCHAR(150),
`cust_details_add3` VARCHAR(150),
`cust_details_town` VARCHAR(150) NOT NULL,
`cust_details_county` VARCHAR(150) NOT NULL,
`cust_details_postcode` VARCHAR(15) NOT NULL,
`cust_details_phone` VARCHAR(15) NOT NULL,
`cust_details_email` VARCHAR(150) NOT NULL,
`cust_details_vat_no` VARCHAR(15),
`cust_details_company_no` VARCHAR(15) UNIQUE,
`cust_details_charity_no` VARCHAR(15) UNIQUE,
`cust_details_special` VARCHAR(1000) DEFAULT 'None',
`cust_details_status` VARCHAR(150) NOT NULL,
`cust_details_sec_check` VARCHAR(150) NOT NULL,
PRIMARY KEY(`cust_details_id`)
);
CREATE TABLE `cust_details_contacts`(
`cust_details_id` INT(5),
`cust_details_contacts_forename` VARCHAR(150) NOT NULL,
`cust_details_contacts_surname` VARCHAR(150) NOT NULL,
`cust_details_contacts_type` VARCHAR(150) NOT NULL,
`cust_details_contacts_email` VARCHAR(150) NOT NULL,
`cust_details_contacts_phone` VARCHAR(15) NOT NULL
);
CREATE TABLE `mtn_agreements`(
`mtn_agreements_id` INT(5) AUTO_INCREMENT,
`cust_details_id` INT(5) NOT NULL,
`mtn_agreements_date_start` DATETIME NOT NULL,
`mtn_agreements_min_term` VARCHAR(3) NOT NULL,
`mtn_agreements_delivery` VARCHAR(65) NOT NULL,
`mtn_agreements_installation` VARCHAR(65) NOT NULL,
`mtn_agreements_collection` VARCHAR(65) NOT NULL,
`mtn_agreements_min_vol` VARCHAR(65),
`mtn_agreements_max_vol` VARCHAR(65) NOT NULL,
`mtn_agreements_inv_frq` VARCHAR(65) NOT NULL,
`mtn_agreements_dss` VARCHAR(65) NOT NULL,
`mtn_agreements_dss_price` VARCHAR(65),
`mtn_agreements_toner` VARCHAR(65) NOT NULL,
`mtn_agreements_cpc_mono` VARCHAR(65) NOT NULL,
`mtn_agreements_cpc_colour` VARCHAR(65),
`mtn_agreements_readings_start` VARCHAR(11) NOT NULL,
`mtn_agreements_readings_end` VARCHAR(11),
`mtn_agreements_pdf_link` VARCHAR(1000) NOT NULL,
PRIMARY KEY(`mtn_agreements_id`)
);
CREATE TABLE `mtn_readings`(
`mtn_readings_id` AUTO_INCREMENT,
`mtn_readings_datetime` DATETIME NOT NULL,
`cust_details_id` INT(5) NOT NULL,
`mfd_id` INT(5) NOT NULL,
`mtn_agreements_id` INT(5) NOT NULL,
`mtn_readings_mono` VARCHAR(11) NOT NULL,
`mtn_readings_colour` VARCHAR(11),
`mtn_readings_type` VARCHAR(65) NOT NULL
);
CREATE TABLE `mfd_devices`(
`mfd_id` INT(5) AUTO_INCREMENT,
`mfd_marketplace_id` INT(5) NOT NULL,
`cust_details_id` INT(5) NOT NULL,
`mfd_serial` VARCHAR(150) NOT NULL,
`mfd_owner` VARCHAR(65) NOT NULL,
`mfd_config` VARCHAR(300) NOT NULL,
`mfd_warranty_length` INT(3),
`mfd_sap_purchase_date` DATETIME,
`mfd_sap_purchase_price` VARCHAR(15)
);
CREATE TABLE `mtn_ticket`(
`mtn_ticket_id` INT(5) AUTO_INCREMENT,
`cust_details_id` INT(5) NOT NULL,
`mtn_agreements_id` INT(5) NOT NULL,
`mfd_id` INT(5) NOT NULL,
`mtn_ticket_description` VARCHAR(10000) NOT NULL,
`mtn_ticket_cats` VARCHAR(150) NOT NULL,
`mtn_ticket_closed` DATETIME NOT NULL,
`mtn_ticket_priority` VARCHAR(65) NOT NULL,
`mtn_ticket_assigned` VARCHAR(150) NOT NULL,
`mtn_ticket_closed` DATETIME,
`mtn_ticket_notes` VARCHAR(10000),
`parts_order_id` INT(5),
PRIMARY KEY(`mtn_ticket_id`)
);
CREATE TABLE `mfd_marketplace`(
`mfd_marketplace_id` INT(5) AUTO_INCREMENT,
`mfd_make` VARCHAR(65) NOT NULL,
`mfd_range` VARCHAR(65) NOT NULL,
`mfd_model` VARCHAR(65) NOT NULL,
`mfd_description` VARCHAR NOT NULL,
`mfd_link_brochure` VARCHAR(1000),
`mfd_link_technical` VARCHAR(1000),
`mfd_status` VARCHAR(65) NOT NULL,
`mfd_price_mfr_rrp` VARCHAR(65),
`mfd_price_sap_rrp` VARCHAR(65),
`mfd_price_sap_sell` VARCHAR(65),
`mfd_price_sap_buy` VARCHAR(65),
PRIMARY KEY(`mfd_marketplace_id`)
);
CREATE TABLE `parts_order`(
`parts_order_id` INT(5) AUTO_INCREMENT,
`parts_order_datetime` DATETIME NOT NULL,
`mtn_agreements_id` INT(5) NOT NULL,
`cust_details_id` INT(5) NOT NULL,
`mfd_devices_id` INT(5) NOT NULL,
PRIMARY KEY(`parts_order_id`)
);
CREATE TABLE `toner_order`(
`toner_order_id` INT(5) AUTO_INCREMENT,
`cust_details_id` INT(5),
` mtn_agreements_id` INT(5),
`toner_order_open` DATETIME NOT NULL,
`toner_order_closed` DATETIME,
`toner_order_delivery_method` VARCHAR(150),
PRIMARY KEY(`toner_order_id`)
);
CREATE TABLE `team_member`(
`team_member_id` INT(5) AUTO_INCREMENT,
`team_member_forename` VARCHAR(65) NOT NULL,
`team_member_surname` VARCHAR(65) NOT NULL,
`team_member_email` VARCHAR(150) NOT NULL,
`team_member_mobile` VARCHAR(15) NOT NULL,
`team_member_dob` VARCHAR(15) NOT NULL,
`team_member_add1` VARCHAR NOT NULL,
`team_member_add2` VARCHAR(150),
`team_member_add3` VARCHAR(150),
`team_member_town` VARCHAR(150),
`team_member_county` VARCHAR(150),
`team_member_postcode` VARCHAR(15),
`team_member_link_cv` VARCHAR(15),
`team_member_link_avatar` VARCHAR(1000),
PRIMARY KEY(`team_member_id`)
);
CREATE TABLE `toner_order_item`(
`toner_order_item_id` INT(10) AUTO_INCREMENT,
`product_toner_id` INT(5) NOT NULL,
`toner_order_id` INT(5) NOT NULL,
`toner_order_item_description` VARCHAR(150) NOT NULL,
`toner_order_item_value` VARCHAR(15),
`toner_order_item_qty` VARCHAR(15),
`toner_order_item_subtotal` VARCHAR(15),
PRIMARY KEY(`toner_order_item_id`)
);
CREATE TABLE `product_toner`(
`product_toner_id` INT(5) AUTO_INCREMENT,
`product_toner_status` VARCHAR(150) NOT NULL,
`product_toner_description` VARCHAR(150) NOT NULL,
`product_toner_price` VARCHAR(15) NOT NULL,
`supplier_id` INT(5) NOT NULL,
`product_toner_yield` VARCHAR(65) NOT NULL,
`product_toner_compatible` VARCHAR(200) NOT NULL,
`product_toner_level` VARCHAR(15) NOT NULL,
`product_toner_reorderat` VARCHAR(15) NOT NULL,
PRIMARY KEY(`product_toner_id`)
);
CREATE TABLE `product_parts`(
`product_parts_id` INT(5) AUTO_INCREMENT,
`product_parts_status` VARCHAR(150) NOT NULL,
`product_parts_description` VARCHAR(150) NOT NULL,
`product_parts_price` VARCHAR(15) NOT NULL,
`supplier_id` INT(5) NOT NULL,
`product_parts_level` VARCHAR(15) NOT NULL,
`product_parts_reorderat` VARCHAR(15) NOT NULL,
PRIMARY KEY(`product_parts_id`)
);
CREATE TABLE `supplier`(
`supplier_id` INT(5) AUTO_INCREMENT,
`supplier_name` VARCHAR(150) NOT NULL,
`supplier_type` VARCHAR(150) NOT NULL,
PRIMARY KEY(`supplier_id`)
);
CREATE TABLE `parts_order_item`(
`parts_order_item_id` INT(5) AUTO_INCREMENT,
`parts_order_id` INT(5) NOT NULL,
`parts_order_item_description` VARCHAR(150) NOT NULL,
`parts_order_item_value` VARCHAR(15),
`parts_order_item_qty` VARCHAR(15),
`parts_order_item_subtotal` VARCHAR(15),
`product_parts_id` INT(5) NOT NULL,
PRIMARY KEY(`parts_order_item_id`)
);
CREATE TABLE `hire_agreement`(
`hire_agreement_id` INT(5) AUTO_INCREMENT,
`hire_agreement_status` VARCHAR(65) NOT NULL,
`hire_agreement_start` DATETIME NOT NULL,
`hire_agreement_end` DATETIME,
`cust_details_id` INT(5) NOT NULL,
`mtn_agreements_id` INT(5) NOT NULL,
`hire_agreement_rate` VARCHAR(15),
`hire_agreement_frequency` VARCHAR(15),
PRIMARY KEY(`hire_agreement_id`)
);
ALTER TABLE
`cust_details_contacts` ADD CONSTRAINT `cust_details_contacts_fk0` FOREIGN KEY(`cust_details_id`) REFERENCES `cust_details`(`cust_details_id`);
ALTER TABLE
`mtn_agreements` ADD CONSTRAINT `mtn_agreements_fk0` FOREIGN KEY(`cust_details_id`) REFERENCES `cust_details`(`cust_details_id`);
ALTER TABLE
`mtn_readings` ADD CONSTRAINT `mtn_readings_fk0` FOREIGN KEY(`cust_details_id`) REFERENCES `cust_details`(`cust_details_id`);
ALTER TABLE
`mtn_readings` ADD CONSTRAINT `mtn_readings_fk1` FOREIGN KEY(`mfd_id`) REFERENCES `mfd_devices`(`mfd_id`);
ALTER TABLE
`mtn_readings` ADD CONSTRAINT `mtn_readings_fk2` FOREIGN KEY(`mtn_agreements_id`) REFERENCES `mtn_agreements`(`mtn_agreements_id`);
ALTER TABLE
`mfd_devices` ADD CONSTRAINT `mfd_devices_fk0` FOREIGN KEY(`mfd_marketplace_id`) REFERENCES `mfd_marketplace`(`mfd_marketplace_id`);
ALTER TABLE
`mfd_devices` ADD CONSTRAINT `mfd_devices_fk1` FOREIGN KEY(`cust_details_id`) REFERENCES `cust_details`(`cust_details_id`);
ALTER TABLE
`mtn_ticket` ADD CONSTRAINT `mtn_ticket_fk0` FOREIGN KEY(`cust_details_id`) REFERENCES `cust_details`(`cust_details_id`);
ALTER TABLE
`mtn_ticket` ADD CONSTRAINT `mtn_ticket_fk1` FOREIGN KEY(`mtn_agreements_id`) REFERENCES `mtn_agreements`(`mtn_agreements_id`);
ALTER TABLE
`mtn_ticket` ADD CONSTRAINT `mtn_ticket_fk2` FOREIGN KEY(`mfd_id`) REFERENCES `mfd_devices`(`mfd_id`);
ALTER TABLE
`mtn_ticket` ADD CONSTRAINT `mtn_ticket_fk3` FOREIGN KEY(`parts_order_id`) REFERENCES `parts_order`(`parts_order_id`);
ALTER TABLE
`parts_order` ADD CONSTRAINT `parts_order_fk0` FOREIGN KEY(`mtn_agreements_id`) REFERENCES `mtn_agreements`(`mtn_agreements_id`);
ALTER TABLE
`parts_order` ADD CONSTRAINT `parts_order_fk1` FOREIGN KEY(`cust_details_id`) REFERENCES `cust_details`(`cust_details_id`);
ALTER TABLE
`parts_order` ADD CONSTRAINT `parts_order_fk2` FOREIGN KEY(`mfd_devices_id`) REFERENCES `mfd_devices`(`mfd_id`);
ALTER TABLE
`toner_order` ADD CONSTRAINT `toner_order_fk0` FOREIGN KEY(`cust_details_id`) REFERENCES `cust_details`(`cust_details_id`);
ALTER TABLE
`toner_order` ADD CONSTRAINT `toner_order_fk1` FOREIGN KEY(` mtn_agreements_id`) REFERENCES `mtn_agreements`(`mtn_agreements_id`);
ALTER TABLE
`toner_order_item` ADD CONSTRAINT `toner_order_item_fk0` FOREIGN KEY(`product_toner_id`) REFERENCES `product_toner`(`product_toner_id`);
ALTER TABLE
`toner_order_item` ADD CONSTRAINT `toner_order_item_fk1` FOREIGN KEY(`toner_order_id`) REFERENCES `toner_order`(`toner_order_id`);
ALTER TABLE
`product_toner` ADD CONSTRAINT `product_toner_fk0` FOREIGN KEY(`supplier_id`) REFERENCES `supplier`(`supplier_id`);
ALTER TABLE
`product_parts` ADD CONSTRAINT `product_parts_fk0` FOREIGN KEY(`supplier_id`) REFERENCES `supplier`(`supplier_id`);
ALTER TABLE
`parts_order_item` ADD CONSTRAINT `parts_order_item_fk0` FOREIGN KEY(`parts_order_id`) REFERENCES `parts_order`(`parts_order_id`);
ALTER TABLE
`parts_order_item` ADD CONSTRAINT `parts_order_item_fk1` FOREIGN KEY(`product_parts_id`) REFERENCES `product_parts`(`product_parts_id`);
That's cause you haven't specified data type for that column for table mtn_readings.
You have
`mtn_readings_id` AUTO_INCREMENT,
Should be
`mtn_readings_id` INT NOT NULL AUTO_INCREMENT,