Help required setting up a database 2

[SQLBungler]

I'm VERY new to SQL and would really like some assistance in setting up a database.

My ER diagram is here:

http://www.alchemistresearch.com/diagram.jpg

I began to enter SQL into Microsoft Access but had quite a few problems. My code is listed below. I'm not sure if it's 100% correct, I imagine that it's not.

CREATE TABLE Assessment (
date_assessed DATETIME NOT NULL,
client_number INTEGER NOT NULL,
staff_assessor_name TEXT (30) NOT NULL,
type_of_facility TEXT(30) NOT NULL,
CONSTRAINT pkassessment PRIMARY KEY (date_assessed, client_number, type_of_facility),
CONSTRAINT fk1assessment FOREIGN KEY (client_number) REFERENCES Client,
CONSTRAINT fk2assessment FOREIGN KEY (type_of_facility) REFERENCES FacilityType
);

CREATE TABLE Client (
client_number INTEGER NOT NULL,
name TEXT(40),
date_of_birth DATETIME,
emergency_contact_number TEXT NOT NULL,
next_of_kin_name TEXT(40),
date_joined DATETIME,
CONSTRAINT pkclient PRIMARY KEY (client_number)
);

CREATE TABLE Equipment (
equipment_reference_number INTEGER NOT NULL,
brand_name TEXT (30),
model_number TEXT (30),
installation_date DATETIME,
type_of_facility TEXT (30),
CONSTRAINT pkequipment PRIMARY KEY (equipment_reference_number),
CONSTRAINT fkequipment FOREIGN KEY (type_of_facility) REFERENCES FacilityType
);

CREATE TABLE FacilityType (
type_of_facility TEXT(30) NOT NULL,
CONSTRAINT pkfacilitytype PRIMARY KEY (type_of_facility)
);

CREATE TABLE Usage (
client_number INTEGER NOT NULL,
date_of_visit DATETIME NOT NULL,
start_time TIME NOT NULL,
end_time TIME NOT NULL,
equipment_reference_number INTEGER NOT NULL,
CONSTRAINT pkusage PRIMARY KEY (start_time,date_of_visit,client_number, equipment_reference_number),
CONSTRAINT fk1usage FOREIGN KEY (client_number,date_of_visit,start_time) REFERENCES Visit,
CONSTRAINT fk2usage FOREIGN KEY (client_number) REFERENCES Client,
CONSTRAINT fk3usage FOREIGN KEY (equipment_reference_number) REFERENCES Equipment
);

CREATE TABLE Visit (
client_number INTEGER NOT NULL,
date_of_visit DATETIME NOT NULL,
entry_time TIME NOT NULL,
exit_time TIME,
CONSTRAINT pkvisit PRIMARY KEY (client_number,date_of_visit,entry_time),
CONSTRAINT fkvisit FOREIGN KEY (client_number) REFERENCES Client
);

Thanks very much,

Sqlbungler.

 

[TonyGroves]

There are a few differences between the two SQL dialects:

- In MySQL, you specify character fields using CHAR, not TEXT.

- Foreign keys are only supported if you're using InnoDB tables. You can set this up as the default in your MySQL configuration, or specify it at table creation time, for example:
[tt]CREATE TABLE t1 ( ... ) TYPE=INNODB;[/tt]

- The syntax CONSTRAINT xxx PRIMARY/FOREIGN KEY can be reduced to simply
PRIMARY/FOREIGN KEY .

Other than that, your code looks fine.

 

[SQLBungler]

Thanks Tony. I've got the MySQL administrator & Table Editor and also MySQL-Front so will attempt to convert them over.

Andy.

 

[ericbrunson]

Also, you must specifically declare an index on your PK in order to have a FK reference it.

 

[SQLBungler]

I think i've just found that out. I can't get the last table (Usage) to be created because the primary keys are also foreign keys from other tables.

I can enter 'client' in this table as a foreign key, but not the others. It just throws up an error and / or crashes MySQL Administrator.

I'm creating the tables in the following order:

Client
FacilityType
Assessment
Visit
Equipment
Usage

Andy.

 

[SQLBungler]

Sorry, wasn't very clear last time.

The problem foreign key is "dateofvisit" in table "usage".

"dateofvisit" is a primary key, along with a number of others in table "visit".

I get MySQL Error 1072
Key column " doesn't exist in table

I think it's trying to pull in the other primary key 'entrytime' but I don't know why or how to fix it. I'm not sure if it's a simple error I am overlooking or a flaw in my ER design.

 

[SQLBungler]

Still encountering problems. I can enter data for all tables except "Usage" where I get an error relating to foreign keys.

My SQL created code is below:

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */;


CREATE DATABASE /*!32312 IF NOT EXISTS*/ `bwfc`;
USE `bwfc`;

CREATE TABLE `assessment` (
  `dateassessed` date NOT NULL default '0000-00-00',
  `clientnumber` int(10) unsigned NOT NULL default '0',
  `typeoffacility` varchar(20) NOT NULL default '',
  `staffassessor` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`dateassessed`,`clientnumber`,`typeoffacility`),
  KEY `FK_Assessment_1` (`clientnumber`),
  KEY `FK_assessment_2` (`typeoffacility`),
  CONSTRAINT `FK_assessment_2` FOREIGN KEY (`typeoffacility`) REFERENCES `facilitytype` (`typeoffacility`),
  CONSTRAINT `FK_Assessment_1` FOREIGN KEY (`clientnumber`) REFERENCES `client` (`clientnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `client` (
  `clientnumber` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(45) NOT NULL default '',
  `dateofbirth` date NOT NULL default '0000-00-00',
  `emergencycontactnumber` varchar(45) NOT NULL default '',
  `nextofkin` varchar(45) NOT NULL default '',
  `datejoined` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`clientnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `equipment` (
  `equipmentreferencenumber` int(10) unsigned NOT NULL auto_increment,
  `brandmake` varchar(45) NOT NULL default '',
  `brnadmodel` varchar(45) NOT NULL default '',
  `installationdate` date NOT NULL default '0000-00-00',
  `typeoffacility` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`equipmentreferencenumber`),
  KEY `FK_Equipment_1` (`typeoffacility`),
  CONSTRAINT `FK_Equipment_1` FOREIGN KEY (`typeoffacility`) REFERENCES `facilitytype` (`typeoffacility`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `facilitytype` (
  `typeoffacility` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`typeoffacility`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `usage` (
  `startime` time NOT NULL default '00:00:00',
  `endtime` time NOT NULL default '00:00:00',
  `dateofvisit` date NOT NULL default '0000-00-00',
  `clientnumber` int(10) unsigned NOT NULL default '0',
  `equipmentreferencenumber` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`startime`,`dateofvisit`,`clientnumber`,`equipmentreferencenumber`),
  KEY `FK_usage_2` (`clientnumber`,`dateofvisit`),
  KEY `FK_usage_3` (`equipmentreferencenumber`),
  CONSTRAINT `FK_usage_3` FOREIGN KEY (`equipmentreferencenumber`) REFERENCES `equipment` (`equipmentreferencenumber`),
  CONSTRAINT `FK_usage_1` FOREIGN KEY (`clientnumber`) REFERENCES `client` (`clientnumber`),
  CONSTRAINT `FK_usage_2` FOREIGN KEY (`clientnumber`, `dateofvisit`) REFERENCES `visit` (`clientnumber`, `dateofvisit`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `visit` (
  `clientnumber` int(10) unsigned NOT NULL auto_increment,
  `dateofvisit` date NOT NULL default '0000-00-00',
  `entrytime` time NOT NULL default '00:00:00',
  `exittime` time NOT NULL default '00:00:00',
  PRIMARY KEY  (`clientnumber`,`dateofvisit`,`entrytime`),
  CONSTRAINT `FK_Visit_1` FOREIGN KEY (`clientnumber`) REFERENCES `client` (`clientnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;