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)
);