Query to add Foreign Key constraint to existing tables?

[Francisco Garcia]

Hi!

I want to create a relationship between two existing tables, but I can't
find the query. I'm using an Access 2000 Database. I've tried these queries
(among many other, all of them fail):

ALTER TABLE [SecurityGroupsObjects] ALTER COLUMN [Object] INTEGER NOT NULL
REFERENCES [SecurityObjects] (Id) ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE [SecurityGroupsObjects] ADD CONSTRAINT ConstName FOREIGN KEY
([Object]) REFERENCES [SecurityObjects] (Id) ON DELETE CASCADE ON UPDATE
CASCADE

Anyone out there can tell me how to do it?

Thanks in advance

 

[peregenem]

I want to create a relationship between two existing tables, but I can't
find the query.

CREATE TABLE Table1 (
key_col_1 INTEGER NOT NULL UNIQUE
)
;
CREATE TABLE Table2 (
key_col_2 INTEGER NOT NULL
)
;
ALTER TABLE Table2
ADD CONSTRAINT fk__table2__table1
FOREIGN KEY (key_col_2)
REFERENCES Table1 (key_col_1)
ON DELETE CASCADE
ON UPDATE CASCADE
;

 

[Francisco Garcia]

Hi,

That was exactly my second option (with different table and field names),
but it doesn't work, I don't know why. I get a "Syntax error in CONSTRAINT
clause", and the DELETE word is automatically selected...

Any ideas?

Thanks

 

[peregenem]

I get a "Syntax error in CONSTRAINT
clause", and the DELETE word is automatically selected

You may need to execute it via the OLE DB provider e.g. in Access2000
VBA use

CurrentProject.Connection.Execute "ALTER TABLE ..."

 

[Francisco Garcia]

My application is written in C#, and I already use the OLE DB provider.

I've also tried to execute the command from within Access 2000, but it
doesn't work either...

 

[peregenem]

My application is written in C#, and I already use the OLE DB provider.

Which one? With Microsoft.Jet.OLEDB.4.0 it should work even for a Jet
3.51 file format but with Microsoft.Jet.OLEDB.3.51 it will fail.

I've also tried to execute the command from within Access 2000, but it
doesn't work either...

But did you use Microsoft.Jet.OLEDB.4.0 ...?

 

[Francisco Garcia]

In C#, I use the "Microsoft.Jet.OLEDB.4.0" managed provider.

In Access (2003), I can create the relationship by opening
Tools\Relationships and dragging one field on top of the other, but it fails
if I try to execute the query from a SQL window.

 

[Brendan Reynolds]

This worked for me in C#. The SQL statements are copied and pasted from
earlier posts in this thread by '(e-mail address removed)', I just added the
C# code to execute them.

using System;
using System.Data;
using System.Data.OleDb;
namespace ConsoleApplication20
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=c:\\dsdata\\db2.mdb;"
+ "Persist Security Info=False";
OleDbConnection connection = new OleDbConnection(connectionString);
try
{
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
string commandString = "CREATE TABLE Table1 (key_col_1 "
+ "INTEGER NOT NULL UNIQUE)";
command.CommandText = commandString;
command.ExecuteNonQuery();
commandString = "CREATE TABLE Table2 (key_col_2 "
+ "INTEGER NOT NULL)";
command.CommandText = commandString;
command.ExecuteNonQuery();
commandString = "ALTER TABLE Table2 ADD "
+ "CONSTRAINT fk__table2__table1 FOREIGN KEY "
+ "(key_col_2) REFERENCES Table1 (key_col_1) ON "
+ "DELETE CASCADE ON UPDATE CASCADE";
command.CommandText = commandString;
command.ExecuteNonQuery();
Console.ReadLine();
}
finally
{
if (connection != null)
if (connection.State != ConnectionState.Closed)
connection.Close();
}
}
}
}

 

[peregenem]

In C#, I use the "Microsoft.Jet.OLEDB.4.0" managed provider.

Do you mean System.Data.OleDb, as per Brendan Reynolds' post? (BTW his
code works fine for me).

In Access (2003), I can create the relationship by opening
Tools\Relationships and dragging one field on top of the other, but it fails
if I try to execute the query from a SQL window.

To be able to execute the SQL in the Access UI, you need to be in 'ANSI
query mode':

http://office.microsoft.com/en-us/assistance/HP030704831033.aspx

 

[Francisco Garcia]

Hi!

Yes, I'm using that data provider, and my code is similar to Brendan
Reynolds' (which is yours).

The problem is that this query seems to work from code, but not from SQL
query window in Access 2003. After having tried different approachs in code,
I tried to find the right query from Access 2003 SQL query window, and this
query always results in a "syntax error". I supposed that SQL syntax was the
same in Access and in Jet OleDB provider, but I was wrong.

Thank you very much for your help

 

[Francisco Garcia]

Thank you, Brendan,

The problem is that this query seems to work from code, but not from SQL
query window in Access 2003. After having tried different approachs in code,
I tried to find the right query from Access 2003 SQL query window, and this
query always results in a "syntax error". I supposed that SQL syntax was the
same in Access and in Jet OleDB provider, but I was wrong.

Thank you very much for your help

This worked for me in C#. The SQL statements are copied and pasted from
earlier posts in this thread by '(e-mail address removed)', I just added the
C# code to execute them.

using System;
using System.Data;
using System.Data.OleDb;
namespace ConsoleApplication20
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=c:\\dsdata\\db2.mdb;"
+ "Persist Security Info=False";
OleDbConnection connection = new OleDbConnection(connectionString);
try
{
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
string commandString = "CREATE TABLE Table1 (key_col_1 "
+ "INTEGER NOT NULL UNIQUE)";
command.CommandText = commandString;
command.ExecuteNonQuery();
commandString = "CREATE TABLE Table2 (key_col_2 "
+ "INTEGER NOT NULL)";
command.CommandText = commandString;
command.ExecuteNonQuery();
commandString = "ALTER TABLE Table2 ADD "
+ "CONSTRAINT fk__table2__table1 FOREIGN KEY "
+ "(key_col_2) REFERENCES Table1 (key_col_1) ON "
+ "DELETE CASCADE ON UPDATE CASCADE";
command.CommandText = commandString;
command.ExecuteNonQuery();
Console.ReadLine();
}
finally
{
if (connection != null)
if (connection.State != ConnectionState.Closed)
connection.Close();
}
}
}
}

--
Brendan Reynolds (MVP)

In C#, I use the "Microsoft.Jet.OLEDB.4.0" managed provider.

In Access (2003), I can create the relationship by opening
Tools\Relationships and dragging one field on top of the other, but it
fails if I try to execute the query from a SQL window.

 

[peregenem]

I supposed that SQL syntax was the
same in Access and in Jet OleDB provider, but I was wrong.

It *is* the same syntax, provide you are in 'ANSI query mode' while in
Access2003 (or XP) - see my earlier link.

 

[Francisco Garcia]

Yes, that link let me see my mistake. I knew about SQL-Passthrough queries,
but not about 'ANSI query mode'.

Thank you so much again for your help