Referential Integrity and Indexes

[Jonathan Scott via AccessMonster.com]

I have a live and a current database and a tool to update the live database
to match the structure of the current database. In this tool, I add DDL
statements, and can also check the indexes, relations, and structure of
invidividual tables.

I can create a FOREIGN KEY reference from one table to another, but am unable
to enforce referential integrity. In my current, I can see the relation in
the tool; but it also seems to have an index on that relation, which seems to
imply that it is this index that is creating the referential integrity. In
other words, my current has a relation with referential integrity, but
through DDL I am only able to create the relationship with no referential
integrity on my live database.

How can I do this through DDL? Or am I forced to manually open the DB, double
click on the line representing the relation, and forcing the referential
integrity flag? (Maybe "referential integrity" is the wrong word, as I am in
a Japanese environment?)

Jonathan Scott

 

[Jonathan Scott via AccessMonster.com]

I should also mention this is in Access97.

I've looked through a book from 95, and it says when I create a FOREIGN KEY
constraint, it should automatically create an index on that relation. However,
my tool tells me that there is not one on the live database after the update.

Jonathan Scott

 

[peregenem]

I have a live and a current database and a tool to update the live database
to match the structure of the current database. In this tool, I add DDL
statements, and can also check the indexes, relations, and structure of
invidividual tables.

I can create a FOREIGN KEY reference from one table to another, but am unable
to enforce referential integrity.

AFAIK creating a FOREIGN KEY using DDL does not automatically create a
unique constraint; you are required to create one before attempting to
create the FK. Here's a demo (I'm using the Jet 4.0 OLE DB provider on
a Jet 3.51 .mdb)

CREATE TABLE Main (
main_key_col INTEGER NOT NULL);

CREATE TABLE Sub (
sub_key_col INTEGER NOT NULL);

ALTER TABLE Sub
ADD FOREIGN KEY (sub_key_col)
REFERENCES Main (main_key_col)
ON DELETE CASCADE
ON UPDATE CASCADE;
-- fails, no unique constraint

ALTER TABLE Main
ADD UNIQUE (main_key_col);

ALTER TABLE Sub
ADD FOREIGN KEY (sub_key_col)
REFERENCES Main (main_key_col)
ON DELETE CASCADE
ON UPDATE CASCADE;
-- success

 

[Tim Ferguson]

I can create a FOREIGN KEY reference from one table to another, but am
unable to enforce referential integrity.

A Foreign Key _is_ an enforced relationship. The acid test is to attempt
to change an FK value to a non-existing key; or else to delete a record
from the related table that is referenced by an existing FK. If the
engine allows it, then Something Bad has happened; if the engine refuses
it, then you have a kosher relationship.

If you had a "ALTER TABLE xxx ADD CONSTRAINT yyyy FOREIGN KEY etc etc"
command, and if it ran without error (you are using dbFailOnError,
right?) then the relationship should be okay.

It's true that you can draw lines in the relationships window in Access
that don't have RI enforced, but these have no effect on the data model
at all and are just one of the meaningless bits put into Access in order
to confuse the newbies (like look up fields, input masks, auto fill in,
etc)

What do you see when you open the _back end_ database (I'm not completely
sure what you mean by current and live databases) and look at the
relationships diagram there? Try clicking the "show all" button first.

Best wishes


Tim F

 

[Jonathan Scott via AccessMonster.com]

Thanks to both for the help. I have found out that if I remove the
relationship and then add it again with DDL, I see the index is being created.


My tool is a db schema diff. It checks relations and indexes. I noticed some
of my indexes had two table names in it, and corresponded with that checkbox
on the relation map's option window. My tool doesn't let me update my live
database unless it is updated correctly.

FYI, "live" refers to the backend system everybody is using in production;
"current" refers to the backend system used in my development environment.
I'm quite happy that, even if it is not really all that convenient, Access
supports DDL that pretty much lets you do everything. (Now, if only I could
twittle that "Allow Zero-length" option in DDL as well).

Once again, thanks for the help!
Jonathan Scott

 

[Tim Ferguson]

(Now, if only I could
twittle that "Allow Zero-length" option in DDL as well).

It's not a part of the standard data model -- it's highly Access-specific.
That is why it's there in DAO but not in standard DDL.

My experiments with SQL Server suggest that

UPDATE Something SET AField = '' WHERE AKey = 1


SELECT AField IS NULL FROM Something WHERE AKey = 1

returns true regardless of what you try to do.

Hope that helps


Tim F

 

[peregenem]

My experiments with SQL Server suggest that

UPDATE Something SET AField = '' WHERE AKey = 1


SELECT AField IS NULL FROM Something WHERE AKey = 1

returns true regardless of what you try to do.

For the latter I get an error, "Incorrect syntax near the keyword 'IS'".

 

[Tim Ferguson]

(e-mail address removed) wrote in @g14g2000cwa.googlegroups.com:

For the latter I get an error, "Incorrect syntax near the
keyword 'IS'".

Okay... I have re-researched this and I was wrong anyway. Here is an
actual example:

1> update bases set fullname=null where baseid=9
2> select baseid, isnull(fullname,'empty') as result
3> from bases
4> where baseid=9
5> go
(1 row affected)
baseid result
----------- --------------------------------------------------
9 empty

(1 row affected)
1> update bases set fullname='' where baseid=9
2> select baseid, isnull(fullname,'empty') as result
3> from bases
4> where baseid=9
5> go
(1 row affected)
baseid result
----------- --------------------------------------------------
9

(1 row affected)

.... so SQL Server seems quite happy to store zero length strings
distinctly from nulls. This is what one would expect although whenever I
tried it before I thought it failed. It is also true that the software
gets in the way -- in Access you have to try very hard to put a zero
length string in a text box.

Still, going back to the OPs original request for DDL equivalent to
"Allow Zero Length Strings" then, as seen above, it's on by default. In
order to refuse zero length strings he'd have to add a check constraint
like


1> alter table bases
2> add constraint NoNullNames
3> check (len(fullname)>0)
4> go
1> insert bases(fullname) values('')
2> go
Msg 547, Level 16, State 1, Server RUBENS, Procedure , Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT statement
conflicted with
COLUMN CHECK constraint 'NoNullNames'. The conflict occurred in database
'NewTestSQL', table 'Bases', column 'FullName'.
The statement has been terminated.


I think that's got it sorted out.


Tim F

 

[peregenem]

We're in agreement then: I posted almost the exact same last week (same
OP?):

http://groups-beta.google.com/group/microsoft.public.access.tablesdbdesign/msg/99bee8bce52ab731