Struggling to figure out a syntax error for a MySQL create table statement in regards to a foreign key reference

Question

I've been wracking my brain for quite a while trying to figure out what is wrong with the first foreign key reference in the Property create table statement below. To isolate the error, I attempted to delete the foreign key reference and just create the table with the second foreign key reference in the Property table (ie: vendorId -> Vendor(vendorId)) and the table was able to be created with no issues. Thus, I believe I may have a design flaw on my hands which has me concerned. Both of the referenced tables have been created as well so that is not the issue. Help would be greatly appreciated, thanks! (Apologies if its some kind of dumb syntax error I'm not seeing)

Error is at line 11 in the Property create table statement: error #1064 You have an error in your SQL syntax Line 11: PRIMARY KEY (pid, vendorId), Line 12: FOREIGN KEY (apptStatus) REFERENCES AgentSchedule(apptStatus)

Here is the code for the Property table referencing the AgentSchedule table as well as the Vendor table

CREATE TABLE Property(
     pid INTEGER, 
     vendorId INTEGER, 
     houseDescr CHAR(150), 
     photoCopy CHAR(5), 
     propertyAddr CHAR(50), 
     apptStatus CHAR(5) DEFAULT 'False',
     numVisits INTEGER, 
     propertyComments CHAR(150), 
     isEmpty CHAR(5),
     PRIMARY KEY (pid, vendorId),
     FOREIGN KEY (apptStatus) REFERENCES AgentSchedule(apptStatus)
         ON DELETE SET DEFAULT
         ON UPDATE CASCADE,
     FOREIGN KEY (vendorId) REFERENCES Vendor(vendorId)
         ON DELETE CASCADE
         ON UPDATE CASCADE,
     CONSTRAINT TOF CHECK(photoCopy IN ('True','False') 
     AND apptStatus IN ('True', 'False') 
     AND isEmpty IN ('True', 'False'))
)

Here is the code for the referenced table corresponding to the first foreign key reference in the Property table.

CREATE TABLE AgentSchedule(
    aid INTEGER,
    propertyId INTEGER,
    apptTime DATE UNIQUE,
    apptStatus CHAR(5),
    sickOrHolidays CHAR(5),
    PRIMARY KEY (aid, propertyId),
    CONSTRAINT TOF CHECK( apptStatus IN ('True', 'False') 
    AND sickOrHolidays IN ('True', 'False'))
)

Here is the referenced table corresponding to the second foreign key reference in the Property table.

CREATE TABLE Vendor(
    vendorId INTEGER,
    name CHAR(50),
    phone INTEGER,
    faxNum INTEGER,
    PRIMARY KEY (vendorId),
    CONSTRAINT PHONE CHECK(Phone >= 1000000000 AND Phone <=  9999999999 
    AND faxNum>= 1000000000 
    AND faxNum <=  9999999999)
)

Are you getting a syntax error or a foreign key error? Please post the exact error. — Barmar, Nov 25 '19 at 22:51
`AgentSchedule.AppStatus` really has a unique key? So you can only have 2 rows in that table, one for `True` and one for `False`. — Barmar, Nov 25 '19 at 22:54
Apologies, I forgot to omit the unique part. No, apptStatus does not have a unique key. — powerhendrix527, Nov 25 '19 at 23:06
Exact error is error #1064 You have an error in your sql syntax. — powerhendrix527, Nov 25 '19 at 23:07
Show the entire error message, it says that the error is near something, we need to know what it's near. — Barmar, Nov 25 '19 at 23:11
It's near this line in the Property create table statement: FOREIGN KEY (apptStatus) REFERENCES AgentSchedule(apptStatus) — powerhendrix527, Nov 25 '19 at 23:15

Nick · Accepted Answer · 2019-11-25T23:22:55.933

Your problem is with the ON DELETE SET DEFAULT clause. Although MySQL recognises that clause, it is not allowed in table definitions. From the manual:

SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

If you remove the SET DEFAULT clause the table creation works fine. Demo. If you need this functionality you can probably implement it via a ON DELETE trigger.

Struggling to figure out a syntax error for a MySQL create table statement in regards to a foreign key reference

1 Answers1