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

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

 

[Brian Camire]

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]

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

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,

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

 

[Brian Camire]

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.

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

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,

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