NO INDEX while creating a foreign key constarint doesn't have any effect?

Ö

Özden Irmak

Hello,

In the Jet Sql 4.0 help file, it's mentioned that when you use NO INDEX
clause in a sql command which is going to cretae a foreign key relationship,
it shouldn't create any index for that column. But it doesn't seem to work.

My SQL command is like that :

ALTER TABLE [Tabel2] ADD CONSTRAINT [Tabel1Tabel2] FOREIGN KEY NO INDEX
([Veld1]) REFERENCES [Tabel1] ([Veld1]);

This clause creates an index with the same name as [Tabel1Tabel2] where it
shouldn't.

Does anybody know any solution for that?

Thanks in advance...

Özden
 
M

Michel Walsh

Hi,



You have to use ADO to execute that command, DAO (and the query
designer) won't work. As example, use the debug-immediate window:


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



Hoping it may help,
Vanderghast, Access MVP
 
Ö

Özden Irmak

Hello Michel,

I execute this statement through ADO Command Object from a VB application
and it still creates an index...

Any other suggestion?

Thanks,

Özden

Michel Walsh said:
Hi,



You have to use ADO to execute that command, DAO (and the query
designer) won't work. As example, use the debug-immediate window:


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



Hoping it may help,
Vanderghast, Access MVP


Özden Irmak said:
Hello,

In the Jet Sql 4.0 help file, it's mentioned that when you use NO INDEX
clause in a sql command which is going to cretae a foreign key relationship,
it shouldn't create any index for that column. But it doesn't seem to work.

My SQL command is like that :

ALTER TABLE [Tabel2] ADD CONSTRAINT [Tabel1Tabel2] FOREIGN KEY NO INDEX
([Veld1]) REFERENCES [Tabel1] ([Veld1]);

This clause creates an index with the same name as [Tabel1Tabel2] where it
shouldn't.

Does anybody know any solution for that?

Thanks in advance...

Özden
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


The following didn't, in Northwind, create an index on temp:


CurrentProject.Connection.Execute "CREATE TABLE temp(f1 VARCHAR(50), f2
LONG, CONSTRAINT f2InCategory FOREIGN KEY NO INDEX (f2) REFERENCES
Categories (CategoryID)) ; "



neither did the following, from a VB6 Standard.exe ( just add a reference
to ADO 2.7, and change the connection string localization of Northwind, if
required ), under the default form created by default for that kind of
project:

=====================
Option Explicit

Private Sub Form_Load()
Dim xnn As ADODB.Connection

Set xnn = New ADODB.Connection
xnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;" & _
"Persist Security Info=False"
xnn.Open

xnn.Execute "CREATE TABLE tempBis(f1 VARCHAR(50), f2 LONG, " & _
" CONSTRAINT f2InCategory FOREIGN KEY NO INDEX (f2) " & _
" REFERENCES Categories (CategoryID)) ; "
End Sub
======================



Hoping it may help,
Vanderghast, Access MVP



Özden Irmak said:
Hello Michel,

I execute this statement through ADO Command Object from a VB application
and it still creates an index...

Any other suggestion?

Thanks,

Özden

Michel Walsh said:
Hi,



You have to use ADO to execute that command, DAO (and the query
designer) won't work. As example, use the debug-immediate window:


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



Hoping it may help,
Vanderghast, Access MVP


Özden Irmak said:
Hello,

In the Jet Sql 4.0 help file, it's mentioned that when you use NO INDEX
clause in a sql command which is going to cretae a foreign key relationship,
it shouldn't create any index for that column. But it doesn't seem to work.

My SQL command is like that :

ALTER TABLE [Tabel2] ADD CONSTRAINT [Tabel1Tabel2] FOREIGN KEY NO INDEX
([Veld1]) REFERENCES [Tabel1] ([Veld1]);

This clause creates an index with the same name as [Tabel1Tabel2]
Click to expand...
Click to expand...
where
it
Click to expand...
Click to expand...
 
Ö

Özden Irmak

Hello Michel,

Thanks for your effort on solving this issue so far...I appreciate it
much...

I executed your code and looked from Access table design whether an index
exists, nope, seems ok. I removed the NO INDEX clause, ran again and checked
the index, there is still no index exist in the design view.

But my claim is that this table has index on both situations (with/without
NO INDEX) ... How can I so sure...?

If you programatically check the index collection (ADOX.Indexes) of this
table, you'll see that there is an index with the same name as the
constraint in that table on both situations.

Any further suggestion?

Best Regards,

Özden

Michel Walsh said:
Hi,


The following didn't, in Northwind, create an index on temp:


CurrentProject.Connection.Execute "CREATE TABLE temp(f1 VARCHAR(50), f2
LONG, CONSTRAINT f2InCategory FOREIGN KEY NO INDEX (f2) REFERENCES
Categories (CategoryID)) ; "



neither did the following, from a VB6 Standard.exe ( just add a reference
to ADO 2.7, and change the connection string localization of Northwind, if
required ), under the default form created by default for that kind of
project:

=====================
Option Explicit

Private Sub Form_Load()
Dim xnn As ADODB.Connection

Set xnn = New ADODB.Connection
xnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;" & _
"Persist Security Info=False"
xnn.Open

xnn.Execute "CREATE TABLE tempBis(f1 VARCHAR(50), f2 LONG, " & _
" CONSTRAINT f2InCategory FOREIGN KEY NO INDEX (f2) " & _
" REFERENCES Categories (CategoryID)) ; "
End Sub
======================



Hoping it may help,
Vanderghast, Access MVP



Özden Irmak said:
Hello Michel,

I execute this statement through ADO Command Object from a VB application
and it still creates an index...

Any other suggestion?

Thanks,

Özden

Michel Walsh said:
Hi,



You have to use ADO to execute that command, DAO (and the query
designer) won't work. As example, use the debug-immediate window:


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



Hoping it may help,
Vanderghast, Access MVP


Hello,

In the Jet Sql 4.0 help file, it's mentioned that when you use NO INDEX
clause in a sql command which is going to cretae a foreign key
relationship,
it shouldn't create any index for that column. But it doesn't seem to
work.

My SQL command is like that :

ALTER TABLE [Tabel2] ADD CONSTRAINT [Tabel1Tabel2] FOREIGN KEY NO INDEX
([Veld1]) REFERENCES [Tabel1] ([Veld1]);

This clause creates an index with the same name as [Tabel1Tabel2]
Click to expand...
where
it
shouldn't.

Does anybody know any solution for that?

Thanks in advance...

Özden
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


That is a good one... I'll ask around to see if someone can enlighten
that...


Vanderghast, Access MVP


Özden Irmak said:
Hello Michel,

Thanks for your effort on solving this issue so far...I appreciate it
much...

I executed your code and looked from Access table design whether an index
exists, nope, seems ok. I removed the NO INDEX clause, ran again and checked
the index, there is still no index exist in the design view.

But my claim is that this table has index on both situations (with/without
NO INDEX) ... How can I so sure...?

If you programatically check the index collection (ADOX.Indexes) of this
table, you'll see that there is an index with the same name as the
constraint in that table on both situations.

Any further suggestion?

Best Regards,

Özden

Michel Walsh said:
Hi,


The following didn't, in Northwind, create an index on temp:


CurrentProject.Connection.Execute "CREATE TABLE temp(f1 VARCHAR(50), f2
LONG, CONSTRAINT f2InCategory FOREIGN KEY NO INDEX (f2) REFERENCES
Categories (CategoryID)) ; "



neither did the following, from a VB6 Standard.exe ( just add a reference
to ADO 2.7, and change the connection string localization of Northwind, if
required ), under the default form created by default for that kind of
project:

=====================
Option Explicit

Private Sub Form_Load()
Dim xnn As ADODB.Connection

Set xnn = New ADODB.Connection
xnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;" & _
"Persist Security Info=False"
xnn.Open

xnn.Execute "CREATE TABLE tempBis(f1 VARCHAR(50), f2 LONG, " & _
" CONSTRAINT f2InCategory FOREIGN KEY NO INDEX (f2) " & _
" REFERENCES Categories (CategoryID)) ; "
End Sub
======================



Hoping it may help,
Vanderghast, Access MVP



Özden Irmak said:
Hello Michel,

I execute this statement through ADO Command Object from a VB application
and it still creates an index...

Any other suggestion?

Thanks,

Özden

Hi,



You have to use ADO to execute that command, DAO (and the query
designer) won't work. As example, use the debug-immediate window:


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



Hoping it may help,
Vanderghast, Access MVP


Hello,

In the Jet Sql 4.0 help file, it's mentioned that when you use NO INDEX
clause in a sql command which is going to cretae a foreign key
relationship,
it shouldn't create any index for that column. But it doesn't seem to
work.

My SQL command is like that :

ALTER TABLE [Tabel2] ADD CONSTRAINT [Tabel1Tabel2] FOREIGN KEY NO INDEX
([Veld1]) REFERENCES [Tabel1] ([Veld1]);

This clause creates an index with the same name as [Tabel1Tabel2] where
it
shouldn't.

Does anybody know any solution for that?

Thanks in advance...

Özden
Click to expand...
Click to expand...
Click to expand...
 
Ö

Özden Irmak

Hello Michel,

I'll apreciate any help and thanks for your attention on this...

I'm the developer of Klik! CompareLib which compares and synchronizes
structural differences in MSAccess databases and in someway I have to find a
solution for this...

Many many thanks again,

Özden

Michel Walsh said:
Hi,


That is a good one... I'll ask around to see if someone can enlighten
that...


Vanderghast, Access MVP


Özden Irmak said:
Hello Michel,

Thanks for your effort on solving this issue so far...I appreciate it
much...

I executed your code and looked from Access table design whether an index
exists, nope, seems ok. I removed the NO INDEX clause, ran again and checked
the index, there is still no index exist in the design view.

But my claim is that this table has index on both situations (with/without
NO INDEX) ... How can I so sure...?

If you programatically check the index collection (ADOX.Indexes) of this
table, you'll see that there is an index with the same name as the
constraint in that table on both situations.

Any further suggestion?

Best Regards,

Özden
Click to expand...
Northwind,
if
required ), under the default form created by default for that kind of
project:

=====================
Option Explicit

Private Sub Form_Load()
Dim xnn As ADODB.Connection

Set xnn = New ADODB.Connection
xnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;" & _
"Persist Security Info=False"
xnn.Open

xnn.Execute "CREATE TABLE tempBis(f1 VARCHAR(50), f2 LONG, " & _
" CONSTRAINT f2InCategory FOREIGN KEY NO INDEX (f2) " & _
" REFERENCES Categories (CategoryID)) ; "
End Sub
======================



Hoping it may help,
Vanderghast, Access MVP



Hello Michel,

I execute this statement through ADO Command Object from a VB application
and it still creates an index...

Any other suggestion?

Thanks,

Özden

Hi,



You have to use ADO to execute that command, DAO (and the query
designer) won't work. As example, use the debug-immediate window:


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



Hoping it may help,
Vanderghast, Access MVP


Hello,

In the Jet Sql 4.0 help file, it's mentioned that when you use NO
INDEX
clause in a sql command which is going to cretae a foreign key
relationship,
it shouldn't create any index for that column. But it doesn't
Click to expand...
seem
to
work.

My SQL command is like that :

ALTER TABLE [Tabel2] ADD CONSTRAINT [Tabel1Tabel2] FOREIGN KEY NO
INDEX
([Veld1]) REFERENCES [Tabel1] ([Veld1]);

This clause creates an index with the same name as [Tabel1Tabel2]
where
it
shouldn't.

Does anybody know any solution for that?

Thanks in advance...

Özden
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


First observations are that with, or without, NO INDEX, in Access 2003,
there is no difference, neither in what ADOX reports, neither in what the
table design shows ! I "suspect" that the option does not work
....anymore... Still to be confirmed, officially.


Vanderghast, Access MVP


Özden Irmak said:
Hello Michel,

I'll apreciate any help and thanks for your attention on this...

I'm the developer of Klik! CompareLib which compares and synchronizes
structural differences in MSAccess databases and in someway I have to find a
solution for this...

Many many thanks again,

Özden

Michel Walsh said:
Hi,


That is a good one... I'll ask around to see if someone can enlighten
that...


Vanderghast, Access MVP


Özden Irmak said:
Hello Michel,

Thanks for your effort on solving this issue so far...I appreciate it
much...

I executed your code and looked from Access table design whether an index
exists, nope, seems ok. I removed the NO INDEX clause, ran again and checked
the index, there is still no index exist in the design view.

But my claim is that this table has index on both situations (with/without
NO INDEX) ... How can I so sure...?

If you programatically check the index collection (ADOX.Indexes) of this
table, you'll see that there is an index with the same name as the
constraint in that table on both situations.

Any further suggestion?

Best Regards,

Özden

Hi,


The following didn't, in Northwind, create an index on temp:


CurrentProject.Connection.Execute "CREATE TABLE temp(f1 VARCHAR(50), f2
LONG, CONSTRAINT f2InCategory FOREIGN KEY NO INDEX (f2) REFERENCES
Categories (CategoryID)) ; "



neither did the following, from a VB6 Standard.exe ( just add a reference
to ADO 2.7, and change the connection string localization of
Click to expand...
Northwind,
if
required ), under the default form created by default for that kind of
project:

=====================
Option Explicit

Private Sub Form_Load()
Dim xnn As ADODB.Connection

Set xnn = New ADODB.Connection
xnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;" & _
"Persist Security Info=False"
xnn.Open

xnn.Execute "CREATE TABLE tempBis(f1 VARCHAR(50), f2 LONG, " & _
" CONSTRAINT f2InCategory FOREIGN KEY NO INDEX (f2) " & _
" REFERENCES Categories (CategoryID)) ; "
End Sub
======================



Hoping it may help,
Vanderghast, Access MVP



Hello Michel,

I execute this statement through ADO Command Object from a VB
application
and it still creates an index...

Any other suggestion?

Thanks,

Özden

Hi,



You have to use ADO to execute that command, DAO (and the query
designer) won't work. As example, use the debug-immediate window:


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



Hoping it may help,
Vanderghast, Access MVP


Hello,

In the Jet Sql 4.0 help file, it's mentioned that when you use NO
INDEX
clause in a sql command which is going to cretae a foreign key
relationship,
it shouldn't create any index for that column. But it doesn't seem
to
work.

My SQL command is like that :

ALTER TABLE [Tabel2] ADD CONSTRAINT [Tabel1Tabel2] FOREIGN KEY NO
INDEX
([Veld1]) REFERENCES [Tabel1] ([Veld1]);

This clause creates an index with the same name as [Tabel1Tabel2]
where
it
shouldn't.

Does anybody know any solution for that?

Thanks in advance...

Özden
Click to expand...
Click to expand...
Click to expand...
 
Ö

Özden Irmak

Just one addition...

It doesn't work on 2000 either...

Özden

Michel Walsh said:
Hi,


First observations are that with, or without, NO INDEX, in Access 2003,
there is no difference, neither in what ADOX reports, neither in what the
table design shows ! I "suspect" that the option does not work
...anymore... Still to be confirmed, officially.


Vanderghast, Access MVP


Özden Irmak said:
Hello Michel,

I'll apreciate any help and thanks for your attention on this...

I'm the developer of Klik! CompareLib which compares and synchronizes
structural differences in MSAccess databases and in someway I have to
Click to expand...
find
a
solution for this...

Many many thanks again,

Özden

VARCHAR(50),
f2
Click to expand...
kind
of
project:

=====================
Option Explicit

Private Sub Form_Load()
Dim xnn As ADODB.Connection

Set xnn = New ADODB.Connection
xnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;" & _
"Persist Security Info=False"
xnn.Open

xnn.Execute "CREATE TABLE tempBis(f1 VARCHAR(50), f2 LONG, " & _
" CONSTRAINT f2InCategory FOREIGN KEY NO INDEX (f2) " & _
" REFERENCES Categories (CategoryID)) ; "
End Sub
======================



Hoping it may help,
Vanderghast, Access MVP



Hello Michel,

I execute this statement through ADO Command Object from a VB
application
and it still creates an index...

Any other suggestion?

Thanks,

Özden

Hi,



You have to use ADO to execute that command, DAO (and the query
designer) won't work. As example, use the debug-immediate window:


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



Hoping it may help,
Vanderghast, Access MVP


Hello,

In the Jet Sql 4.0 help file, it's mentioned that when you
Click to expand...
use
NO
INDEX
clause in a sql command which is going to cretae a foreign key
relationship,
it shouldn't create any index for that column. But it
Click to expand...
doesn't
seem
to
work.

My SQL command is like that :

ALTER TABLE [Tabel2] ADD CONSTRAINT [Tabel1Tabel2] FOREIGN
Click to expand...
KEY
NO
INDEX
([Veld1]) REFERENCES [Tabel1] ([Veld1]);

This clause creates an index with the same name as [Tabel1Tabel2]
where
it
shouldn't.

Does anybody know any solution for that?

Thanks in advance...

Özden
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

To Access support/development team - NO INDEX clause in SQL Constraint still creates an index? Is it 2
How to make one-to-one relation via code? 3
Foreign key with SET NULL: possible? 5
Constraint Syntax Error? 1
Some advice? 3
Foreign Key self-join on a DataTable 2
Binding Combobox to Foreign Key in Datagrid 3
CHECK constraint usage 10

Top