0

I have an issue that I can't figure out for the life of me. I have been creating an application in PHP/MySQL on windows using XAMPP but now I am trying to test it on a Linux based LAMP server and I get the following error when trying to create a table with foreign key constraints:

"3 - photoProjectItem table creation: Cannot add foreign key constraint"

When I run the code on XAMPP in Windows it works fine, but not within Linux. These are the tables I am trying to create:

        CREATE TABLE IF NOT EXISTS generalSecurity(
            id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(100) UNIQUE NOT NULL,
            password VARCHAR(100) NOT NULL,
            firstname VARCHAR(100) NOT NULL,
            secondname VARCHAR(100) NOT NULL,
            accessLevel ENUM('admin', 'contributer', 'subscriber') NOT NULL,
            Email VARCHAR (200) UNIQUE NOT NULL
        ) ENGINE=INNODB;

        CREATE TABLE IF NOT EXISTS generalSiteInfo (
            id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            siteName VARCHAR(100) NOT NULL,
            siteOwnerID INT(10) UNSIGNED NOT NULL,
            INDEX par_id4(siteOwnerID),
            FOREIGN KEY(siteOwnerID)
                REFERENCES generalSecurity(id)
                ON DELETE CASCADE,
            siteEmail VARCHAR(100) NOT NULL,
            siteAbout VARCHAR(9999) NOT NULL,
            currentTheme VARCHAR(1000) NOT NULL,
            siteCreateDate TIMESTAMP
                DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=INNODB;

        CREATE TABLE IF NOT EXISTS Project (
            id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            projectAuthorID INT(10) UNSIGNED NOT NULL,
            INDEX par_id(projectAuthorID),
            FOREIGN KEY(projectAuthorID)
                REFERENCES generalSecurity(id)
                ON DELETE CASCADE,
            projectName VARCHAR(100) NOT NULL,
            projectBlurb VARCHAR(5000) NULL,
            projectTheme VARCHAR(100) NOT NULL,
            projectDate TIMESTAMP
        ) ENGINE=INNODB;

    CREATE TABLE IF NOT EXISTS ProjectItem (
            id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            authorID INT(10) UNSIGNED NOT NULL,
            INDEX par_id2(authorID),
            FOREIGN KEY(authorID)
                REFERENCES generalSecurity(id)
                ON DELETE CASCADE,
            projectID INT(10) UNSIGNED NOT NULL,
            INDEX par_id3(projectID),
            FOREIGN KEY(projectID)
                REFERENCES project(id)
                ON DELETE CASCADE,
            itemType ENUM('image', 'text') NOT NULL,
            photoFileName VARCHAR(100) NULL,
            itemName VARCHAR(1000) NOT NULL,
            entry VARCHAR(5000) NULL,
            photoMeta VARCHAR(5000) NOT NULL,
            date TIMESTAMP,
            deleted BOOLEAN NOT NULL
        ) ENGINE=INNODB;

it fails when trying to create table "ProjectItem". Can you guys see anything I am missing?

I really appreciate any help that can be given

---edit---

it works when I remove the lines

           INDEX par_id3(projectID),
        FOREIGN KEY(projectID)
            REFERENCES project(id)
            ON DELETE CASCADE,

but I can't see any obvious issues with the relationship

TimoneUK
  • 31
  • 7

1 Answers1

1

The issue was that apparantly MYSQL in Linux is case sensitive whereas in Windows it doesn't appear to be. I was trying to create a relationship for the table "project" when it is called "Project"

TimoneUK
  • 31
  • 7