Access/Jet SQL - Problems with deferred constraints
constraints = "ALTER TABLE Control ";
constraints += "ADD CONSTRAINT fk_ctrl_loc ";
constraints += "FOREIGN KEY (Location) REFERENCES Location(LocIndex) ";
constraints += "INITIALLY DEFERRABLE DEFERRED ";
try {
pNewDB->ExecuteSQL( constraints );
AfxMessageBox("New Tables Create");
}
catch ( CDBException *e ) {
AfxMessageBox(e->m_strError, MB_ICONEXCLAMATION);
e->Delete();
}
This throws an exception every time. The error says "Syntax error in ALTER TABLE statement".
Any idea what I could be doing wrong? Minus the last line "INITIALLY DEFERRABLE DEFERRED" the alter table statement executes without problem.
Gol
[715 byte] By [
Golovko] at [2007-11-19 11:09:30]
# 1 Re: Access/Jet SQL - Problems with deferred constraints
Well, using CDatabase::ExecuteSQL to execute:
constraints = "ALTER TABLE Location ADD CONSTRAINT fk_loc_ctrl ";
constraints += "FOREIGN KEY (Control) REFERENCES Control(CtrlIndex) DEFERRABLE INITIALLY DEFERRED";
gives the error "Syntax in ALTER TABLE statment"
executing:
constraints = "ALTER TABLE Location ADD CONSTRAINT fk_loc_ctrl ";
constraints += "FOREIGN KEY (Control) REFERENCES Control(CtrlIndex) DEFERRABLE ";
works fine, what gives?
# 2 Re: Access/Jet SQL - Problems with deferred constraints
From what I read not even SQL Server supports deferred constraints. Please please correct me if I am wrong. This is disappointing to read, so I am no longer trying to fix the above but rather working around it by moving it down into the actual C++ code to check for errors.
