MySQL - Can't create Visit table

noislude

edit: the EER model link to the EER Model Hi, good morning from Brazil. What's the right way to create the Visit table below?

CREATE TABLE IF NOT EXISTS FARM
(
FARM_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
FARM_NAME VARCHAR(45) NOT NULL,
ADDRESS VARCHAR(45) NOT NULL,
OWNER VARCHAR(45) NOT NULL
)ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS PADDOCK
(
PADDOCK_NUM INT NOT NULL,
FARM_FARM_ID INT NOT NULL,
PRIMARY KEY(FARM_FARM_ID, PADDOCK_NUM),
AREA VARCHAR(45) NOT NULL,
CONSTRAINT FARM_FARM_ID_CON FOREIGN KEY(FARM_FARM_ID)
REFERENCES FARM(FARM_ID)
)ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS VISIT
(
VISIT_ID INT NOT NULL PRIMARY KEY,
GATHERING DATE,
PADDOCK_PADDOCK_NUM INT NOT NULL,
PADDOCK_FARM_ID INT NOT NULL,
CONSTRAINT PADDOCK_PADDOCK_NUM_CON FOREIGN KEY(PADDOCK_PADDOCK_NUM)
REFERENCES PADDOCK(PADDOCK_NUM),
CONSTRAINT PADDOCK_PADDOCK_FARM_ID_CON FOREIGN KEY(PADDOCK_FARM_ID)
REFERENCES PADDOCK(FARM_FARM_ID)
)ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS VISIT
(
VISIT_ID INT NOT NULL PRIMARY KEY,
GATHERING DATE,
PADDOCK_PADDOCK_NUM INT NOT NULL,
PADDOCK_FARM_ID INT NOT NULL,
CONSTRAINT PADDOCK_PADDOCK_NUM_CON FOREIGN KEY(PADDOCK_PADDOCK_NUM)
REFERENCES PADDOCK(PADDOCK_NUM),
CONSTRAINT PADDOCK_PADDOCK_FARM_ID_CON FOREIGN KEY(PADDOCK_FARM_ID)
REFERENCES PADDOCK(FARM_FARM_ID),
UNIQUE KEY(PADDOCK_PADDOCK_NUM, PADDOCK_FARM_ID, GATHERING)
)ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS SAMPLE
(
SAMPLE_NUM INT UNSIGNED NOT NULL AUTO_INCREMENT,
VISIT_VISIT_ID INT NOT NULL,
PRIMARY KEY(SAMPLE_NUM, VISIT_VISIT_ID),
COUNT INT,
CONSTRAINT VISIT_VISIT_ID_CON FOREIGN KEY(VISIT_VISIT_ID)
REFERENCES VISIT(VISIT_ID)
)ENGINE = INNODB;

PIEBALDconsult

What database system?

noislude

>I created an empty database for MySQL: paddock.

CREATE DATABASE PADDOCK;
USE PADDOCK;

Mycroft Holmes

Just a thought, I don't know MySQL, but is there a constraint where you cannot have an object the same name as the database. EF has such a constraint on field/table names.

Never underestimate the power of human stupidity RAH

noislude

sorry. In fact, I created the database paddock in lowercase:

create database paddock;
use paddock;

(I think I was writing some SQL code at that moment with uppercase)

Bernhard Hiller

The difference between your two statements is the additional key in the second statement. Keys are important for searching data/retrieving data. So it depends on how you would normally get the data from that table. Also note that a References statement may not cause the creation of a key - I am not sure here with MySQL. So for the JOINs with the other tables, additional Keys could be appropriate.

Richard Deeming

You haven't specified what error you're getting when you run the script. For a start, you've got two CREATE TABLE IF NOT EXISTS VISIT blocks - only the first one will run. I'm not overly familiar with MySQL, but in SQL you'd need to include both columns in a single foreign key:

CONSTRAINT PADDOCK_FARM_ID_PADDOCK_NUM_CON (PADDOCK_FARM_ID, PADDOCK_PADDOCK_NUM)
REFERENCES PADDOCK (FARM_FARM_ID, PADDOCK_NUM)

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Lost User

noislude wrote:

I created the database paddock in lowercase:

The server doesn't care about upper- or lowercase; the name is case-insensitive. Same goes for tables and column-names.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]