Add constraint returns syntax error on constrain clause

S

Shnizles

hi everyone ,
i am trying to add a constraint to a table ,
as the ms access guides suggested i have crafted the following query:
ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY
([calendarid]) REFERENCES Calendar ([id]) ON DELETE CASCADE

the above query result in a syntax error ,
the problem seems to be "ON DELETE CASCADE" , when i remove it the
relationship is being added just fine , however the constraint must have
Delete cascade enabled ,

can anyone point me to the problem ,how can i solve it ,
thank you for your help!
 
M

Michel Walsh

Have you tried using ADO?

CurrentProject.Connection.Execute "ALTER TABLE Holiday ADD CONSTRAINT
CalendarHoliday FOREIGN KEY (calendarid) REFERENCES Calendar (id) ON DELETE
CASCADE"


in the immediate (debug) window...

Once added, the constraint will work as well in DAO than in ADO, but it is
just that many Jet 4.0 extensions work only under ADO, like here, adding a
cascading referential integrity.



Vanderghast, Access MVP
 
S

Shnizles

im not using vba to excute the queries ,
im using vb.net ,
and well i tried to connect with OLEDB and ADODB connectors
both resulted with the same errors ,
as well when i run the sql query in access 2007 sql design tab ,
same error.

thanks for the response any other ideas?

Michel Walsh said:
Have you tried using ADO?

CurrentProject.Connection.Execute "ALTER TABLE Holiday ADD CONSTRAINT
CalendarHoliday FOREIGN KEY (calendarid) REFERENCES Calendar (id) ON DELETE
CASCADE"


in the immediate (debug) window...

Once added, the constraint will work as well in DAO than in ADO, but it is
just that many Jet 4.0 extensions work only under ADO, like here, adding a
cascading referential integrity.



Vanderghast, Access MVP


Shnizles said:
hi everyone ,
i am trying to add a constraint to a table ,
as the ms access guides suggested i have crafted the following query:
ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY
([calendarid]) REFERENCES Calendar ([id]) ON DELETE CASCADE

the above query result in a syntax error ,
the problem seems to be "ON DELETE CASCADE" , when i remove it the
relationship is being added just fine , however the constraint must have
Delete cascade enabled ,

can anyone point me to the problem ,how can i solve it ,
thank you for your help!
Click to expand...
Click to expand...
 
M

Michel Walsh

I am not in a position to test it right now, and my experience with DotNet
is with MS SQL Server, rather than with Jet, but I would have tried (sorry,
it is in C# ) something like :


// references
using System;
using System.Data;
using System.Data.SqlClient; // to be changed if you use Jet


// ... in your code ...

String connectionString = "Provider=... " // ADO.Net connection string
// probably something like "Provider=Microsoft.Jet.OLEDB.4.0;
....
// in your case

using ( SqlConnection xnn = new SqlConnection(connectionString) )
{
System.Diagnostics.Debug.Assert(
null != xnn,
"Invalid connection string ? ");

xnn.Open() ;
System.Diagnostics.Debug.Assert(
xnn.State == ConnectionState.Open,
"Cannot open the db ? busy? poor connectivity ? ");

using ( SqlCommand cmd = xnn.CreateCommand() )
{
cmd.CommandType= CommandType.Text ;
cmd.CommandString = "ALTER TABLE ... " ;

cmd.ExecuteNonQuery() ;
}
}



Note: You have to change the data type for xnn, and cmd, here, they are
specific for MS SQL Server.

Vanderghast, Access MVP



Shnizles said:
im not using vba to excute the queries ,
im using vb.net ,
and well i tried to connect with OLEDB and ADODB connectors
both resulted with the same errors ,
as well when i run the sql query in access 2007 sql design tab ,
same error.

thanks for the response any other ideas?

Michel Walsh said:
Have you tried using ADO?

CurrentProject.Connection.Execute "ALTER TABLE Holiday ADD CONSTRAINT
CalendarHoliday FOREIGN KEY (calendarid) REFERENCES Calendar (id) ON
DELETE
CASCADE"


in the immediate (debug) window...

Once added, the constraint will work as well in DAO than in ADO, but it
is
just that many Jet 4.0 extensions work only under ADO, like here, adding
a
cascading referential integrity.



Vanderghast, Access MVP


Shnizles said:
hi everyone ,
i am trying to add a constraint to a table ,
as the ms access guides suggested i have crafted the following query:
ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY
([calendarid]) REFERENCES Calendar ([id]) ON DELETE CASCADE

the above query result in a syntax error ,
the problem seems to be "ON DELETE CASCADE" , when i remove it the
relationship is being added just fine , however the constraint must
have
Delete cascade enabled ,

can anyone point me to the problem ,how can i solve it ,
thank you for your help!
Click to expand...
Click to expand...
Click to expand...
 
S

Shnizles

hi , thanks for the efort ,
i know how to set a connection to a database ,
the problem is the error i get no matter what i do.
:|

Michel Walsh said:
I am not in a position to test it right now, and my experience with DotNet
is with MS SQL Server, rather than with Jet, but I would have tried (sorry,
it is in C# ) something like :


// references
using System;
using System.Data;
using System.Data.SqlClient; // to be changed if you use Jet


// ... in your code ...

String connectionString = "Provider=... " // ADO.Net connection string
// probably something like "Provider=Microsoft.Jet.OLEDB.4.0;
....
// in your case

using ( SqlConnection xnn = new SqlConnection(connectionString) )
{
System.Diagnostics.Debug.Assert(
null != xnn,
"Invalid connection string ? ");

xnn.Open() ;
System.Diagnostics.Debug.Assert(
xnn.State == ConnectionState.Open,
"Cannot open the db ? busy? poor connectivity ? ");

using ( SqlCommand cmd = xnn.CreateCommand() )
{
cmd.CommandType= CommandType.Text ;
cmd.CommandString = "ALTER TABLE ... " ;

cmd.ExecuteNonQuery() ;
}
}



Note: You have to change the data type for xnn, and cmd, here, they are
specific for MS SQL Server.

Vanderghast, Access MVP



Shnizles said:
im not using vba to excute the queries ,
im using vb.net ,
and well i tried to connect with OLEDB and ADODB connectors
both resulted with the same errors ,
as well when i run the sql query in access 2007 sql design tab ,
same error.

thanks for the response any other ideas?

Michel Walsh said:
Have you tried using ADO?

CurrentProject.Connection.Execute "ALTER TABLE Holiday ADD CONSTRAINT
CalendarHoliday FOREIGN KEY (calendarid) REFERENCES Calendar (id) ON
DELETE
CASCADE"


in the immediate (debug) window...

Once added, the constraint will work as well in DAO than in ADO, but it
is
just that many Jet 4.0 extensions work only under ADO, like here, adding
a
cascading referential integrity.



Vanderghast, Access MVP


hi everyone ,
i am trying to add a constraint to a table ,
as the ms access guides suggested i have crafted the following query:
ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY
([calendarid]) REFERENCES Calendar ([id]) ON DELETE CASCADE

the above query result in a syntax error ,
the problem seems to be "ON DELETE CASCADE" , when i remove it the
relationship is being added just fine , however the constraint must
have
Delete cascade enabled ,

can anyone point me to the problem ,how can i solve it ,
thank you for your help!
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

I should have asked, first, if the statement works in Access itself, in the
immediate debug window? if not, then, with the User Interface itself? Maybe
there is a problem with the design, such as a missing key, or something.



Vanderghast, Access MVP


Shnizles said:
hi , thanks for the efort ,
i know how to set a connection to a database ,
the problem is the error i get no matter what i do.
:|

Michel Walsh said:
I am not in a position to test it right now, and my experience with
DotNet
is with MS SQL Server, rather than with Jet, but I would have tried
(sorry,
it is in C# ) something like :


// references
using System;
using System.Data;
using System.Data.SqlClient; // to be changed if you use Jet


// ... in your code ...

String connectionString = "Provider=... " // ADO.Net connection
string
// probably something like
"Provider=Microsoft.Jet.OLEDB.4.0;
....
// in your case

using ( SqlConnection xnn = new SqlConnection(connectionString) )
{
System.Diagnostics.Debug.Assert(
null != xnn,
"Invalid connection string ? ");

xnn.Open() ;
System.Diagnostics.Debug.Assert(
xnn.State == ConnectionState.Open,
"Cannot open the db ? busy? poor connectivity ? ");

using ( SqlCommand cmd = xnn.CreateCommand() )
{
cmd.CommandType= CommandType.Text ;
cmd.CommandString = "ALTER TABLE ... " ;

cmd.ExecuteNonQuery() ;
}
}



Note: You have to change the data type for xnn, and cmd, here, they are
specific for MS SQL Server.

Vanderghast, Access MVP



Shnizles said:
im not using vba to excute the queries ,
im using vb.net ,
and well i tried to connect with OLEDB and ADODB connectors
both resulted with the same errors ,
as well when i run the sql query in access 2007 sql design tab ,
same error.

thanks for the response any other ideas?

:

Have you tried using ADO?

CurrentProject.Connection.Execute "ALTER TABLE Holiday ADD CONSTRAINT
CalendarHoliday FOREIGN KEY (calendarid) REFERENCES Calendar (id) ON
DELETE
CASCADE"


in the immediate (debug) window...

Once added, the constraint will work as well in DAO than in ADO, but
it
is
just that many Jet 4.0 extensions work only under ADO, like here,
adding
a
cascading referential integrity.



Vanderghast, Access MVP


hi everyone ,
i am trying to add a constraint to a table ,
as the ms access guides suggested i have crafted the following
query:
ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY
([calendarid]) REFERENCES Calendar ([id]) ON DELETE CASCADE

the above query result in a syntax error ,
the problem seems to be "ON DELETE CASCADE" , when i remove it the
relationship is being added just fine , however the constraint must
have
Delete cascade enabled ,

can anyone point me to the problem ,how can i solve it ,
thank you for your help!
Click to expand...
Click to expand...
Click to expand...
 
S

Shnizles

when i use access 2007 , i go to create sql query
i enter the following line:
ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY
([calendarid]) REFERENCES Calendar ([id])

it runs fine , i go to database tools tab and viewing relationships ,
i can see the relationship have been created successfuly ,
i can then double click the relation and manualy check the Cascade delete
check box
which means there is no problem with key or design what so ever ,
cause it works like that.

but however if i create a query in the access 2007 sql section with the
following line:

ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY
([calendarid]) REFERENCES Calendar ([id]) ON DELETE CASCADE

Then i get syntax error on constrain clause which keeps amazing me everytime.
let me know if i can asist with more info,
thanks.

Michel Walsh said:
I should have asked, first, if the statement works in Access itself, in the
immediate debug window? if not, then, with the User Interface itself? Maybe
there is a problem with the design, such as a missing key, or something.



Vanderghast, Access MVP


Shnizles said:
hi , thanks for the efort ,
i know how to set a connection to a database ,
the problem is the error i get no matter what i do.
:|

Michel Walsh said:
I am not in a position to test it right now, and my experience with
DotNet
is with MS SQL Server, rather than with Jet, but I would have tried
(sorry,
it is in C# ) something like :


// references
using System;
using System.Data;
using System.Data.SqlClient; // to be changed if you use Jet


// ... in your code ...

String connectionString = "Provider=... " // ADO.Net connection
string
// probably something like
"Provider=Microsoft.Jet.OLEDB.4.0;
....
// in your case

using ( SqlConnection xnn = new SqlConnection(connectionString) )
{
System.Diagnostics.Debug.Assert(
null != xnn,
"Invalid connection string ? ");

xnn.Open() ;
System.Diagnostics.Debug.Assert(
xnn.State == ConnectionState.Open,
"Cannot open the db ? busy? poor connectivity ? ");

using ( SqlCommand cmd = xnn.CreateCommand() )
{
cmd.CommandType= CommandType.Text ;
cmd.CommandString = "ALTER TABLE ... " ;

cmd.ExecuteNonQuery() ;
}
}



Note: You have to change the data type for xnn, and cmd, here, they are
specific for MS SQL Server.

Vanderghast, Access MVP



im not using vba to excute the queries ,
im using vb.net ,
and well i tried to connect with OLEDB and ADODB connectors
both resulted with the same errors ,
as well when i run the sql query in access 2007 sql design tab ,
same error.

thanks for the response any other ideas?

:

Have you tried using ADO?

CurrentProject.Connection.Execute "ALTER TABLE Holiday ADD CONSTRAINT
CalendarHoliday FOREIGN KEY (calendarid) REFERENCES Calendar (id) ON
DELETE
CASCADE"


in the immediate (debug) window...

Once added, the constraint will work as well in DAO than in ADO, but
it
is
just that many Jet 4.0 extensions work only under ADO, like here,
adding
a
cascading referential integrity.



Vanderghast, Access MVP


hi everyone ,
i am trying to add a constraint to a table ,
as the ms access guides suggested i have crafted the following
query:
ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY
([calendarid]) REFERENCES Calendar ([id]) ON DELETE CASCADE

the above query result in a syntax error ,
the problem seems to be "ON DELETE CASCADE" , when i remove it the
relationship is being added just fine , however the constraint must
have
Delete cascade enabled ,

can anyone point me to the problem ,how can i solve it ,
thank you for your help!
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Maybe an example will be more useful about what I have in mind :)


Using Northwind, if you try, in the query designer:

ALTER TABLE Products ADD CONSTRAINT ProductInCategory FOREIGN KEY
([categoryID]) REFERENCES Categories ([categoryID]) ON DELETE CASCADE


you get a syntax error. In fact, the query designer, by default, works with
DAO and DAO is not aware of many Jet 4.0 extensions.


Always in Northwind, this time, in the immediate (debug) window, generally
open with Ctrl_G,


CurrentProject.Connection.Execute "ALTER TABLE Products ADD CONSTRAINT
ProductInCategory FOREIGN KEY ([categoryID]) REFERENCES Categories
([categoryID]) ON DELETE CASCADE"



(it is one line, the news reader probably make it two or three lines )

then, you still get an error, but NOT a syntax error anymore, but a conflict
error:

"The cascading options for the new reference conflicts with existing
reference 'CategoriesProducts'"


That is why I asked you to tried in the Immediate (Debug) window, rather
than through the query designer... to see if Access is REALLY in a mood to
agree to accept the cascading constraint. If not, well, ...


If there is no error while using the immediate (debug) window, using ADO as
proposed, then I am clueless about what is wrong with doing the same, from
DotNet and ADONet.





Vanderghast, Access MVP



Shnizles said:
when i use access 2007 , i go to create sql query
i enter the following line:
ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY
([calendarid]) REFERENCES Calendar ([id])

it runs fine , i go to database tools tab and viewing relationships ,
i can see the relationship have been created successfuly ,
i can then double click the relation and manualy check the Cascade delete
check box
which means there is no problem with key or design what so ever ,
cause it works like that.

but however if i create a query in the access 2007 sql section with the
following line:

ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY
([calendarid]) REFERENCES Calendar ([id]) ON DELETE CASCADE

Then i get syntax error on constrain clause which keeps amazing me
everytime.
let me know if i can asist with more info,
thanks.

Michel Walsh said:
I should have asked, first, if the statement works in Access itself, in
the
immediate debug window? if not, then, with the User Interface itself?
Maybe
there is a problem with the design, such as a missing key, or something.



Vanderghast, Access MVP


Shnizles said:
hi , thanks for the efort ,
i know how to set a connection to a database ,
the problem is the error i get no matter what i do.
:|

:

I am not in a position to test it right now, and my experience with
DotNet
is with MS SQL Server, rather than with Jet, but I would have tried
(sorry,
it is in C# ) something like :


// references
using System;
using System.Data;
using System.Data.SqlClient; // to be changed if you use Jet


// ... in your code ...

String connectionString = "Provider=... " // ADO.Net connection
string
// probably something like
"Provider=Microsoft.Jet.OLEDB.4.0;
....
// in your case

using ( SqlConnection xnn = new SqlConnection(connectionString) )
{
System.Diagnostics.Debug.Assert(
null != xnn,
"Invalid connection string ? ");

xnn.Open() ;
System.Diagnostics.Debug.Assert(
xnn.State == ConnectionState.Open,
"Cannot open the db ? busy? poor connectivity ?
");

using ( SqlCommand cmd = xnn.CreateCommand() )
{
cmd.CommandType= CommandType.Text ;
cmd.CommandString = "ALTER TABLE ... " ;

cmd.ExecuteNonQuery() ;
}
}



Note: You have to change the data type for xnn, and cmd, here, they
are
specific for MS SQL Server.

Vanderghast, Access MVP



im not using vba to excute the queries ,
im using vb.net ,
and well i tried to connect with OLEDB and ADODB connectors
both resulted with the same errors ,
as well when i run the sql query in access 2007 sql design tab ,
same error.

thanks for the response any other ideas?

:

Have you tried using ADO?

CurrentProject.Connection.Execute "ALTER TABLE Holiday ADD
CONSTRAINT
CalendarHoliday FOREIGN KEY (calendarid) REFERENCES Calendar (id)
ON
DELETE
CASCADE"


in the immediate (debug) window...

Once added, the constraint will work as well in DAO than in ADO,
but
it
is
just that many Jet 4.0 extensions work only under ADO, like here,
adding
a
cascading referential integrity.



Vanderghast, Access MVP


hi everyone ,
i am trying to add a constraint to a table ,
as the ms access guides suggested i have crafted the following
query:
ALTER TABLE Holiday ADD CONSTRAINT CalendarHoliday FOREIGN KEY
([calendarid]) REFERENCES Calendar ([id]) ON DELETE CASCADE

the above query result in a syntax error ,
the problem seems to be "ON DELETE CASCADE" , when i remove it
the
relationship is being added just fine , however the constraint
must
have
Delete cascade enabled ,

can anyone point me to the problem ,how can i solve it ,
thank you for your help!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cascade delete 1
Please Help - Syntax error in - ON UPDATE 0
Using DDL to Create Constraint - Syntax Error 4
Droping constraint 8
Syntax error in CONSTRAINT 1
Query to add Foreign Key constraint to existing tables? 12
ALTER TABLE syntax 2
SQL DDL 1

Top