Unique Foreign Key constraint?!?!

[Jonathan Scott via AccessMonster.com]

I have an application who's backend has a relationship defined one to one. I
need to update the LIVE version of the database to reflect this for the new
version. How can I express such a foreign key constraint in DDL? I tried
creating a foreign key constraint, and then making a unique index on the
field in the foreign table, and vice versa, and neither has worked. My schema
diff utility tells me there is still a one to many relationship at the end of
the update.

Any help would be highly appreciated!
Jonathan Scott

 

[Jonathan Scott via AccessMonster.com]

I've tried now to set the Attributes member of the Relation to
dbRelationUnique, but I keep getting an error saying that the operation is
not permitted. Why can I not set the property on the table? The data itself
is not violating the 1-to-1 relationship, so I am stumped as to how to force
Access to make that Relation be 1-to-1!

Jonathan Scott

 

[Jonathan Scott via AccessMonster.com]

So, now I am trying a different way; I will make the relationship through DAO.

Public Sub createOneToOneRelation(dbConnection As Database, SQL As String)
Dim relationDefinition As Relation
Dim relationName As String
Dim tableName, columnName, foreignTableName, foreignColumnName As String
Dim commandArgs As Collection

Set commandArgs = argumentsOfCommandAsCollection(SQL)
relationName = commandArgs(1)
tableName = commandArgs(2)
columnName = commandArgs(3)
foreignTableName = commandArgs(4)
foreignColumnName = commandArgs(5)
Set relationDefinition = dbConnection.CreateRelation(relationName,
tableName, foreignTableName)
With relationDefinition
.Fields.Append dbConnection.TableDefs(foreignTableName).Fields
(foreignColumnName)
.Fields(foreignColumnName).ForeignName = columnName
.Attributes = dbRelationUnique
End With
dbConnection.Relations.Append relationDefinition
End Sub

The Fields.Append line causes an error saying there is already an item with
that name. Comment it out, and the next line causes an error saying there is
no such item in the collection. WTF is up with this?

Jonathan Scott

I've tried now to set the Attributes member of the Relation to
dbRelationUnique, but I keep getting an error saying that the operation is
not permitted. Why can I not set the property on the table? The data itself
is not violating the 1-to-1 relationship, so I am stumped as to how to force
Access to make that Relation be 1-to-1!

Jonathan Scott

I have an application who's backend has a relationship defined one to one. I
need to update the LIVE version of the database to reflect this for the new

[quoted text clipped - 6 lines]

Any help would be highly appreciated!
Jonathan Scott

 

[peregenem]

So, now I am trying a different way; I will make the relationship through DAO.

For DDL you are better off using ADO because you can use the Jet 4.0
DDL syntax; DAO DDL development stopped at Jet 3. Also, IIRC, when
creating a FOREIGN KEY with DAO it tries to be 'clever' and create
(unique) indexes with out being invited, which has caused me headaches
in the past. The OLE DB provider, which ADO uses, does not exhibit this
behavior: you have to explicitly create the UNIQUE constraints before
creating the FK but at least *you* are in control.

Here's what I think you are trying to achieve but with Jet 4.0 DDL:

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

 

[Tim Ferguson]

How can I express such a foreign key constraint in DDL? I tried
creating a foreign key constraint, and then making a unique index on
the field in the foreign table, and vice versa, and neither has
worked.

(a) It's rare to need a 1:1 relationship in a well designed database --
are you sure you really need to do this?

(b) The easiest and most straightforward way is to connect the PKs of
the two table:

CREATE TABLE ParentTable (
ParentNum INTEGER NOT NULL PRIMARY KEY,
etc...)


CREATE TABLE ChildTable (
Parent INTEGER NOT NULL PRIMARY KEY
CONSTRAINT BelongsTo FOREIGN KEY REFERENCES ParentTable(ParentNum),
etc...)


Beware, I haven't checked the syntax, but you get the picture.

If for some reason you want to use non-primary key fields, then you will
have to fix a number of things first. The foreign key field in the
ChildTable has to have a non-nulls unique index (otherwise it's not
going to be 1:1). The target field in the ParentTable has to have a
unique index (otherwise the db engine won't know which record is being
referenced). All these constraints make for a huge update and insert
headache.


Hope it helps


Tim F

 

[peregenem]

All these constraints make for a huge update and insert
headache.

These constraints are (presumably) to ensure data integrity. If the
data does not fit, the constraint will bite and the UPDATE/INSERT will
rollback. To try to do the same without using constraints in the
database could lead to a severe headache-inducing situation.