[Closed] a bit OT: any Database / SQL people around?

Is that a copy and paste error => "ACTIO" ?

ON DELETE NO [b]ACTION[/b]
ON UPDATE NO [b]ACTIO[/b]

corresponds to your MySQL server version for the right syntax to use near ' <=

MSQL uses 'near' to signify that what follows is the first thing it can't understand. From what the OP has posted this would appear to be the single quote character.

ERROR 1064 (42000) at line 157: 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 ')
REFERENCES nhs1.Treatment ()
ON DELETE NO ACTION
ON UPDATE NO ' at line 40

not sure why the weird paste error, I've just re-ran the export after making some minor updates, I'll go back again and check as obviously it's now saying 'Treatment' not 'Patient' (in backtick, but I'm using a Mac with a pc keyboard and haven't quite figured that out yet...sigh)

I'd avoid copy/pasting text like that. Export to a file, move the file across (scp) then import from the file.

Yeah, have tried the export to a .sql file but still nada.

It's a 'backtick' and is legal for use in mysql, I should hope so as I'm using the latest versions of mysql workbench + mysql server 5.7

I have the fact table with each column entry except the first (which is a primary key) linked/associated by way of a foreign key to 8 other tables (dimension tables).

totally stumped ;(

I'm not familiar with workbench, but can't you just do this from the command line?

mysqldump > export_file.sql

It's a 'backtick' and is legal for use in mysql

REFERENCES nhs1.Treatment ()
ON DELETE NO ACTION
ON UPDATE NO ' at line 40

Which of these lines is 157?

What do you mean a backtick, I can't see that in the script. I feel like we are working blind.

Should it not be
[b]ON UPDATE NO ACTION[/b]
?

What do you mean a backtick, I can't see that in the script.

I just assumed he was referring to the single quote (') - which isn't a backtick (`)

I feel like we are working blind.

ok, here's the resultant .sql file
https://gist.github.com/anonymous/3bcc1da249c30bd10e662577fac7c174

and many thanks so far!!

That looks fine. Are you trying to import it doing

mysql -u username -p database_name < star_schema

?

thanks @orangespyderman. I have done it via command line and the gui, still no joy ;(

Just tried it again and still the same error message.

Here's the workbench file: https://drive.google.com/file/d/0BxFCRQ9DShDkV1hGMkdKalJXWkE/view?usp=sharing just incase there's something visible there.

The dimension tables are created, the fact table is not. So I must have something weird set up - I just can't find it ;(

and still the same error message.

Have you imported [b]that[/b] file (the exact same one) from the command line and what error did it generate, and what was actually in the file on your system at the line mysql complained about?

Hey. Patient was pre-amendment to my workbench file, so it is now Treatment. No, the error remains consistently the same regardless of whether I scp the file over, copy/paste or using mysql workbench's 'forward engineer' (and subsequent 'import' function).

Yeah, I don't know why line 13 is mentioned either.

What's at line 157? a comment. So if I guess at removing the comments from the file and referencing the error:

ERROR 1064 (42000) at line 157: 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 ')
REFERENCES nhs1.Treatment ()
ON DELETE NO ACTION
ON UPDATE NO ' at line 40

It's this block of code:

CREATE TABLE IF NOT EXISTS nhs1.Treatment_Fact (
TreatmentFact_ID INT NOT NULL,
Patient_ID INT NULL,
Unit_ID INT NULL,
Staff_ID INT NULL,
Operation_ID INT NULL,
Drug_ID INT NULL,
ContCare_ID INT NULL,
DrugStock_ID INT NULL,
Treat_ID INT NULL,
Treatment_Date DATETIME NULL,
PRIMARY KEY (TreatmentFact_ID),
INDEX Operation_ID_idx (Operation_ID ASC),
INDEX Drug_ID_idx (Drug_ID ASC),
INDEX ContCare_ID_idx (ContCare_ID ASC),
INDEX Staff_ID_idx (Staff_ID ASC),
INDEX Treat_ID_idx (Treat_ID ASC),
INDEX Unit_ID_idx (Unit_ID ASC),
CONSTRAINT Patient_ID
FOREIGN KEY (Patient_ID)
REFERENCES nhs1.Patient (Patient_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT Treat_ID
FOREIGN KEY (Treat_ID)
REFERENCES nhs1.Treatment (Treat_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT Unit_ID
FOREIGN KEY (Unit_ID)
REFERENCES nhs1.Treatment_Unit (Unit_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT Staff_ID
FOREIGN KEY (Staff_ID)
REFERENCES nhs1.Staff (Staff_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT Treat_WaitTime
FOREIGN KEY ()
REFERENCES nhs1.Treatment ()
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT Operation_ID
FOREIGN KEY (Operation_ID)
REFERENCES nhs1.Operation (Operation_Code)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT Drug_ID
FOREIGN KEY (Drug_ID)
REFERENCES nhs1.Treatment_Drug (Drug_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT ContCare_ID
FOREIGN KEY (ContCare_ID)
REFERENCES nhs1.Continuing_Care (ContCare_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT Drug_ID
FOREIGN KEY (Drug_ID)
REFERENCES nhs1.Treatment_Drug (Drug_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

which is, according to my text editor, line 41.

ta.

OK, update. I removed a column from the fact table which was titled "Treatment_WaitTime" and I now get a different error (at command line)

ERROR 1215 (HY000) at line 157: Cannot add foreign key constraint

;(

OK, update. I removed a column from the fact table which was titled "Treatment_WaitTime" and I now get a different error (at command line)

ERROR 1215 (HY000) at line 157: Cannot add foreign key constraint

Right. I really don't get what you're doing, nor why. I don't know anything about the workbench so, as a disclaimer, if this is tool specific I will not be able to help. However, you say you removed a "column" from the "fact" table that was called "Treatment_WaitTime"

I can't find a "column" called that in the SQL you shared. I can find a field called Treat_WaitTime that was in Treatment, and I can find a constraint in the definition of Treatment_Fact called Treat_WaitTime. It does give the impression, from where I'm sitting, that you're not very methodological. Apologies if that offends, but it would make me look more for user error than a "bug"...

If you remove random fields from the schema design I would expect errors like the one you just posted (foreign keys etc; why did you decide Treatment_WaitTime (whatever that is) was an issue?).

Can I ask (again) if you have just tried using mysqldump on the source DB and have you cleared the target database instance between import attempts?

CONSTRAINT Treat_WaitTime
FOREIGN KEY ()
REFERENCES nhs1.Treatment ()

This constraint doesn't have a column or a foreign key defined like the rest do. Take it out, it's not doing anything and it's probably causing your error

I guess it was referencing the Treatment_WaitTime column? Although that was on the FACT table not the TREATMENT one?

A foreign key constraint is looking in another table to verify the data exists of course. If it doesn't, it won't go in. Does that wait time exist in a waittime table elsewhere?

You seem to have the constraint "Drug_ID" defined twice

When he gets to the point of doing an insert... we aren't there yet!

'Wait time', unless it's the ID from a list of pre-determined wait times, doesn't sound like it should be a foreign key. Sounds like just a bit of data on this table.

Have I wondered into Stack Overflow? 😀

Actually probably not, the replies are far too helpful 😉

😀

Aaaand you've got DrugStock_ID which looks like a foreign key but no actual constraint to go with it.

I'm assuming you've made a choice with which columns to index?

A bit of space and ordering things properly helps

(by the way I don't use MySQL so providing no guarantees!)

CREATE TABLE IF NOT EXISTS nhs1.Treatment_Fact (
TreatmentFact_ID INT NOT NULL,
Patient_ID INT NULL,
Unit_ID INT NULL,
Staff_ID INT NULL,
Operation_ID INT NULL,
Drug_ID INT NULL,
ContCare_ID INT NULL,
DrugStock_ID INT NULL,
Treat_ID INT NULL,
Treatment_Date DATETIME NULL,
----------------------------------------------
PRIMARY KEY (TreatmentFact_ID),
INDEX Unit_ID_idx (Unit_ID ASC),
INDEX Staff_ID_idx (Staff_ID ASC),
INDEX Operation_ID_idx (Operation_ID ASC),
INDEX Drug_ID_idx (Drug_ID ASC),
INDEX ContCare_ID_idx (ContCare_ID ASC),
INDEX Treat_ID_idx (Treat_ID ASC),
----------------------------------------------
CONSTRAINT Patient_ID
FOREIGN KEY (Patient_ID)
REFERENCES nhs1.Patient (Patient_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
----------------------------------------------
CONSTRAINT Unit_ID
FOREIGN KEY (Unit_ID)
REFERENCES nhs1.Treatment_Unit (Unit_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
----------------------------------------------
CONSTRAINT Staff_ID
FOREIGN KEY (Staff_ID)
REFERENCES nhs1.Staff (Staff_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
----------------------------------------------
CONSTRAINT Operation_ID
FOREIGN KEY (Operation_ID)
REFERENCES nhs1.Operation (Operation_Code)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
----------------------------------------------
CONSTRAINT Drug_ID
FOREIGN KEY (Drug_ID)
REFERENCES nhs1.Treatment_Drug (Drug_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
----------------------------------------------
CONSTRAINT ContCare_ID
FOREIGN KEY (ContCare_ID)
REFERENCES nhs1.Continuing_Care (ContCare_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
----------------------------------------------
CONSTRAINT Treat_ID
FOREIGN KEY (Treat_ID)
REFERENCES nhs1.Treatment (Treat_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
ENGINE = InnoDB;

I've run this entire script without error so this should work!

-- MySQL Script generated by MySQL Workbench
-- Tue Dec 13 14:41:45 2016
-- Model: New Model Version: 1.0
-- 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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema nhs1
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema nhs1
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS nhs1 DEFAULT CHARACTER SET utf8 ;
USE nhs1 ;

-- -----------------------------------------------------
-- Table nhs1.Patient
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS nhs1.Patient (
Patient_ID INT NOT NULL,
Patient_DoB DATE NULL,
Patient_Country VARCHAR(70) NULL,
Patient_City VARCHAR(70) NULL,
Patient_Occupation VARCHAR(70) NULL,
Patient_Name VARCHAR(255) NULL,
Patient_TelNo VARCHAR(10) NULL,
Patient_Address VARCHAR(255) NULL,
Patient_Email VARCHAR(255) NULL,
Patient_County VARCHAR(70) NULL,
Treat_ID VARCHAR(70) NULL,
Op_Code INT NULL,
Patient_NextO**** VARCHAR(255) NULL,
Patient_QtyOfMissedAppts INT NULL,
ContdCare_ID INT NULL,
Patient_AtRisk VARCHAR(45) NULL,
PRIMARY KEY (Patient_ID))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table nhs1.Treatment_Unit
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS nhs1.Treatment_Unit (
Unit_ID INT NOT NULL,
Unit_Type VARCHAR(45) NULL,
Unit_County VARCHAR(70) NULL,
Unit_City VARCHAR(70) NULL,
Treatment_Cost VARCHAR(45) NULL,
PRIMARY KEY (Unit_ID))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table nhs1.Staff
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS nhs1.Staff (
Staff_ID INT NOT NULL,
Staff_Level VARCHAR(20) NULL,
Staff_Specialisation VARCHAR(70) NULL,
Staff_Name VARCHAR(255) NULL,
Staff_WeeklyHrs INT NULL,
Staff_Salary INT NULL,
Staff_OvertimeRate INT NULL,
Staff_OnCall TINYINT(1) NULL,
Unit_City VARCHAR(70) NULL,
PRIMARY KEY (Staff_ID))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table nhs1.Drug_Stock
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS nhs1.Drug_Stock (
DrugStock_ID INT NOT NULL,
DrugStock_Cost VARCHAR(45) NULL,
DrugStock_Type VARCHAR(45) NULL,
DrugStock_Dosage VARCHAR(45) NULL,
DrugStock_Manufacturer VARCHAR(45) NULL,
DrugStock_Restrictions VARCHAR(45) NULL,
DrugStock_Trial VARCHAR(45) NULL,
DrugStock_ReceivedDate DATE NULL,
DrugStock_IssueDate VARCHAR(45) NULL,
DrugStock_ExpiryDate VARCHAR(45) NULL,
PRIMARY KEY (DrugStock_ID))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table nhs1.Treatment
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS nhs1.Treatment (
Treat_ID INT NOT NULL,
Treat_Type VARCHAR(45) NULL,
Patient_ID VARCHAR(45) NULL,
Diagnosis VARCHAR(45) NULL,
Unit_Code VARCHAR(45) NULL,
Staff_ID VARCHAR(45) NULL,
Treat_WaitTime DATETIME NULL,
PRIMARY KEY (Treat_ID))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table nhs1.Operation
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS nhs1.Operation (
Operation_Code INT NOT NULL,
Operation_Type VARCHAR(45) NULL,
Operation_Cost VARCHAR(45) NULL,
Operation_Time VARCHAR(45) NULL,
Operation_Equipment VARCHAR(45) NULL,
Operation_Budget VARCHAR(45) NULL,
Patient_ID INT NULL,
Staff_ID INT NULL,
Unit_Code INT NULL,
PRIMARY KEY (Operation_Code))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table nhs1.Treatment_Drug
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS nhs1.Treatment_Drug (
Drug_ID INT NOT NULL,
TD_Date VARCHAR(45) NULL,
TD_Duration VARCHAR(45) NULL,
TD_Cost VARCHAR(45) NULL,
Patient_ID VARCHAR(45) NULL,
Unit_Code VARCHAR(45) NULL,
PRIMARY KEY (Drug_ID))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table nhs1.Continuing_Care
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS nhs1.Continuing_Care (
ContCare_ID INT NOT NULL,
Patient_ID VARCHAR(45) NULL,
Unit_Code VARCHAR(45) NULL,
Staff_ID VARCHAR(45) NULL,
Treat_ID VARCHAR(45) NULL,
ContCare_ApptDate VARCHAR(45) NULL,
ContCare_ApptLength VARCHAR(45) NULL,
ContCare_TotalNumberOfAppts VARCHAR(45) NULL,
PRIMARY KEY (ContCare_ID))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table nhs1.Treatment_Fact
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS nhs1.Treatment_Fact (
TreatmentFact_ID INT NOT NULL,
Patient_ID INT NULL,
Unit_ID INT NULL,
Staff_ID INT NULL,
Operation_ID INT NULL,
Drug_ID INT NULL,
ContCare_ID INT NULL,
DrugStock_ID INT NULL,
Treat_ID INT NULL,
Treatment_Date DATETIME NULL,
PRIMARY KEY (TreatmentFact_ID),
INDEX Operation_ID_idx (Operation_ID ASC),
INDEX Drug_ID_idx (Drug_ID ASC),
INDEX ContCare_ID_idx (ContCare_ID ASC),
INDEX Staff_ID_idx (Staff_ID ASC),
INDEX Treat_ID_idx (Treat_ID ASC),
INDEX Unit_ID_idx (Unit_ID ASC),
CONSTRAINT Patient_ID
FOREIGN KEY (Patient_ID)
REFERENCES nhs1.Patient (Patient_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT Treat_ID
FOREIGN KEY (Treat_ID)
REFERENCES nhs1.Treatment (Treat_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT Unit_ID
FOREIGN KEY (Unit_ID)
REFERENCES nhs1.Treatment_Unit (Unit_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT Staff_ID
FOREIGN KEY (Staff_ID)
REFERENCES nhs1.Staff (Staff_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT Treat_WaitTime
FOREIGN KEY (Treat_ID)
REFERENCES nhs1.Treatment (Treat_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT Operation_ID
FOREIGN KEY (Operation_ID)
REFERENCES nhs1.Operation (Operation_Code)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT ContCare_ID
FOREIGN KEY (ContCare_ID)
REFERENCES nhs1.Continuing_Care (ContCare_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT Drug_ID
FOREIGN KEY (Drug_ID)
REFERENCES nhs1.Treatment_Drug (Drug_ID)
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;

muchas gracias 😉 all sorted now (well, late last night)

I think it was as simple as the foreign keys not being correctly set - it's been a while since I have done any sql stuff from scratch and it was definitely a case of 'cant see the wood for the trees'.

Now building sample / dummy data, it's a bit tedious!

thanks all!

If you need anything to base the dummy data on look at the DH's reference cost datasets, tariff price lists and pbr excluded drug lists. Also, opcs codes for treatments and icd10 codes for diagnosiseseses.

(Apologies if you already know about all of those, they just might save you hours of making stuff up!)

hmm, bails ... I'm using something called Mockaroo to generate the data which handily has ICD10 or ICD9 codes.

I did not know about the OPCS codes and that's enormously helpful - exactly what I need!!! Thank You!