Error Code: 1005 Can't create table

[johnnyturbo3]

Hi,

Trying to run this script via MySQL workbench. Can anyone see anything wrong with it? I suspect the foreign keys are not defined properly?

Thanks

USE streamlibrary;

DROP TABLE IF EXISTS streamlibrary.FileProperties ;

CREATE  TABLE IF NOT EXISTS streamlibrary.FileProperties(
  PK_fileName VARCHAR(50) NOT NULL,
  extension VARCHAR(4) NOT NULL ,
  sizeMB DECIMAL(3) NOT NULL ,
  location VARCHAR(50) NOT NULL ,
  data VARCHAR(45) NOT NULL ,
  name VARCHAR(45) NOT NULL ,
  PRIMARY KEY (PK_fileName)
  );

-- -----------------------------------------------------
-- Table streamlibrary.AudioCodec
-- -----------------------------------------------------
DROP TABLE IF EXISTS streamlibrary.AudioCodec;

CREATE  TABLE IF NOT EXISTS streamlibrary.AudioCodec(
  PK_audioCodecID INT NOT NULL AUTO_INCREMENT,
  format VARCHAR(20) NOT NULL,
  bitRate INT NOT NULL,
  PRIMARY KEY (PK_audioCodecID)
  );
  


-- -----------------------------------------------------
-- Table streamlibrary.Audio
-- -----------------------------------------------------
DROP TABLE IF EXISTS streamlibrary.Audio;

CREATE  TABLE IF NOT EXISTS streamlibrary.Audio(
  PK_audioID INT NOT NULL AUTO_INCREMENT ,
  fileName VARCHAR(50) NOT NULL ,
  codec INT NOT NULL ,
  sampleRate VARCHAR(4) NOT NULL ,
  channel VARCHAR(45) NOT NULL ,
  content VARCHAR(45) NOT NULL,
  PRIMARY KEY (PK_audioID),
  FOREIGN KEY (fileName) REFERENCES FileProperties(fileName),
  FOREIGN KEY (codec) REFERENCES AudioCodec(PK_audioCodecID)
  );

[tesuji]

Hello,

there is a wrong primary key reference in your code, examine red-coloured lines:

USE streamlibrary;

DROP TABLE IF EXISTS streamlibrary.FileProperties ;

CREATE  TABLE IF NOT EXISTS streamlibrary.FileProperties(
  PK_fileName VARCHAR(50) NOT NULL,
  extension VARCHAR(4) NOT NULL ,
  sizeMB DECIMAL(3) NOT NULL ,
  location VARCHAR(50) NOT NULL ,
  data VARCHAR(45) NOT NULL ,
  name VARCHAR(45) NOT NULL ,
  PRIMARY KEY (PK_fileName)
  );

-- -----------------------------------------------------
-- Table streamlibrary.AudioCodec
-- -----------------------------------------------------
DROP TABLE IF EXISTS streamlibrary.AudioCodec;

CREATE  TABLE IF NOT EXISTS streamlibrary.AudioCodec(
  PK_audioCodecID INT NOT NULL AUTO_INCREMENT,
  format VARCHAR(20) NOT NULL,
  bitRate INT NOT NULL,
  PRIMARY KEY (PK_audioCodecID)
  );
  


-- -----------------------------------------------------
-- Table streamlibrary.Audio
-- -----------------------------------------------------
DROP TABLE IF EXISTS streamlibrary.Audio;

CREATE  TABLE IF NOT EXISTS streamlibrary.Audio(
  PK_audioID INT NOT NULL AUTO_INCREMENT ,
  fileName VARCHAR(50) NOT NULL ,
  codec INT NOT NULL ,
  sampleRate VARCHAR(4) NOT NULL ,
  channel VARCHAR(45) NOT NULL ,
  content VARCHAR(45) NOT NULL,
  PRIMARY KEY (PK_audioID),
  FOREIGN KEY (fileName) REFERENCES FileProperties(fileName),
  FOREIGN KEY (codec) REFERENCES AudioCodec(PK_audioCodecID)
  );

Be sure that your database engine is always innodb. If not, foreign keys are entirely useless.

Btw, if there exists a many-to-many relationship between FileProperties and AudioCodec, then primary key of Audio, which obviously is the linking table to making up this relationship, must be at least (PK_fileName, codec). Sometimes this pair of column names is not unique, therefore "at least", additional column must be added to pk. If there are doubts on my suggestion, ask me.

-- tesu

[johnnyturbo3]

I think I can see my error. Sorry for wasting your time with a typo.

My intention was to create a 1:many with FileProperties and Audio. Audio and AudioCodec are a 1:1.

[johnnyturbo3]

How do I change the thread to solved?

[phpstan]

See http://www.daniweb.com/forums/thread53024.html
then give it a try. I'll check back to see if you were able to flag it.
Here is the part of the link that seems to be what you are looking for:
In this modification the original poster (or Admins and Mods) have an extra option under Thread Tools that allows them to mark the thread Solved.

[phpstan]

I found how to change the thread as Solved.
Go the bottom of the thread as though you are going to post a reply. Just above the reply area you (only the person that starts the thread) has a link labeled "Mark this Thread as Solved". Click that link and it will be flagged as Solved.

When it is solved, you will see a new link above the reply area label "Mark this Thread as Unsolved" that you can use to re-open the thread.