How to make one-to-one relation via code?

  • Thread starter Thread starter Özden Irmak
  • Start date Start date
Ö

Özden Irmak

Hello,

I'm trying to establish a one to one relation via code in 2 tables. Both
tables contains one primary key columns and with the following code I can
create one to many relationship :

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

I think that it should be something like that but it doesn't work and gives
syntax error :

ALTER TABLE [Tabel2] ADD CONSTRAINT [Tabel1Tabel2] UNIQUE [Veld1] REFERENCES
[Tabel1] ([Veld1]);

Thanks in advance...

Özden
 
You use the same syntax to define a one-to-one relationship as you do for a
one-to-many relationship. Whether it's one-to-one or one-to-many is
determined by whether or not the "child" table has a unique constraint,
unique index, or primary key on the fields involved.

In other words, if you define a unique constraint on "Veld1" in "Tabel2"
using a statement like:

ALTER TABLE [Tabel2] ADD CONSTRAINT [Secondary Key] UNIQUE ([Veld1])

then creating a relation using the first syntax from your post (plus the
parentheses around the field list after the FOREIGN KEY keywords, which I
think you need):

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

will create a one-to-one relation.
 
Hello Brian,

Thanks for your valuable info...

Unfortuanately, your solution didn't help me to achieve my goal as I think
I've forgot to mention some points.

I have to create the foregin key relationship and unique key with the same
name. In your suggestion their names are going to be different.

My solution was first creating a unique key with the name [Tabel1Tabel2] and
then adding a constraint with the same name to create the relationship but
this causes an error saying that "An index with the same name already exists
in the table" . Tried to use NO INDEX clause while creating the relationship
but this seems have no effect as I still get the same error.

Thanks in advance for all your help...

Özden

Brian Camire said:
You use the same syntax to define a one-to-one relationship as you do for a
one-to-many relationship. Whether it's one-to-one or one-to-many is
determined by whether or not the "child" table has a unique constraint,
unique index, or primary key on the fields involved.

In other words, if you define a unique constraint on "Veld1" in "Tabel2"
using a statement like:

ALTER TABLE [Tabel2] ADD CONSTRAINT [Secondary Key] UNIQUE ([Veld1])

then creating a relation using the first syntax from your post (plus the
parentheses around the field list after the FOREIGN KEY keywords, which I
think you need):

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

will create a one-to-one relation.

Özden Irmak said:
Hello,

I'm trying to establish a one to one relation via code in 2 tables. Both
tables contains one primary key columns and with the following code I can
create one to many relationship :

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

I think that it should be something like that but it doesn't work and gives
syntax error :

ALTER TABLE [Tabel2] ADD CONSTRAINT [Tabel1Tabel2] UNIQUE [Veld1] REFERENCES
[Tabel1] ([Veld1]);

Thanks in advance...

Özden
Click to expand...
Click to expand...
 
I don't think you can create two different constraints on the same table
with the same name. I don't think this limitation is specific to Access.

Özden Irmak said:
Hello Brian,

Thanks for your valuable info...

Unfortuanately, your solution didn't help me to achieve my goal as I think
I've forgot to mention some points.

I have to create the foregin key relationship and unique key with the same
name. In your suggestion their names are going to be different.

My solution was first creating a unique key with the name [Tabel1Tabel2] and
then adding a constraint with the same name to create the relationship but
this causes an error saying that "An index with the same name already exists
in the table" . Tried to use NO INDEX clause while creating the relationship
but this seems have no effect as I still get the same error.

Thanks in advance for all your help...

Özden

Brian Camire said:
You use the same syntax to define a one-to-one relationship as you do
Click to expand...
for
a
one-to-many relationship. Whether it's one-to-one or one-to-many is
determined by whether or not the "child" table has a unique constraint,
unique index, or primary key on the fields involved.

In other words, if you define a unique constraint on "Veld1" in "Tabel2"
using a statement like:

ALTER TABLE [Tabel2] ADD CONSTRAINT [Secondary Key] UNIQUE ([Veld1])

then creating a relation using the first syntax from your post (plus the
parentheses around the field list after the FOREIGN KEY keywords, which I
think you need):

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

will create a one-to-one relation.

Özden Irmak said:
Hello,

I'm trying to establish a one to one relation via code in 2 tables. Both
tables contains one primary key columns and with the following code I can
create one to many relationship :

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

I think that it should be something like that but it doesn't work and gives
syntax error :

ALTER TABLE [Tabel2] ADD CONSTRAINT [Tabel1Tabel2] UNIQUE [Veld1] REFERENCES
[Tabel1] ([Veld1]);

Thanks in advance...

Özden
Click to expand...
Click to expand...
Click to expand...
 
Back
Top