Solved

Access Table Constraints Built using ADO

Posted on 2008-10-31
6
436 Views
Last Modified: 2013-11-29
My Cableroutings table models a many-to-many relationship.  However, I am trying to constrain this relationship to limit the number of occurrences of any given Cable to a maximum of 2 occurrences.  (Basically a cable can only be routed to two enclosures).

There is a problem.  My 'CHECK_ONLY_2_ROUTES_FOR_CABLE' doesn't work as I've defined it.  It only allows me to create two records in the entire table.  This isn't what I want.  

Does anyone know how I am reformulate my check constraint to achieve the desired functionality?
--Table In question

CREATE TABLE Cableroutings (

    areacode TEXT( 4 ) NOT NULL,

   enc_class TEXT( 3 ) NOT NULL,

     enc_nbr INTEGER NOT NULL,

      prefix INTEGER NOT NULL,

   cabletype TEXT( 2 ) NOT NULL,

      suffix INTEGER NOT NULL,
 

  CONSTRAINT FKEY_Enclosures_Cablerouting

 FOREIGN KEY ( areacode, enc_class, enc_nbr )

  REFERENCES Enclosures( areacode, enc_class, enc_nbr )

   ON UPDATE CASCADE,
 

  CONSTRAINT FKEY_Cables_Cablerouting

 FOREIGN KEY ( prefix, cabletype, suffix )

  REFERENCES Cables( prefix, cabletype, suffix )

   ON UPDATE CASCADE );
 

--Access doesn't like CHECK constraints declared in Table Definition

--But declaring them with ALTER TABLE ADD ... works just fine.

     ALTER TABLE Cableroutings

  ADD CONSTRAINT CHECK_ONLY_2_ROUTES_FOR_CABLE

  CHECK(( SELECT COUNT( * )

            FROM Cableroutings AS CR

           WHERE CR.prefix = prefix

             AND CR.cabletype = cabletype

             AND CR.suffix = suffix ) BETWEEN 0 AND 2 );

Open in new window

0
Comment
Question by:rabroersma
  • 3
  • 3
6 Comments
 
LVL 10

Expert Comment

by:LennyGray
ID: 22851598
You may want to consider redesigning the table for the Cable and include the following fields:

CableID as Long Integer
CableName as String
CableEnclosureDestinationOne as Long Interger (holds the index of an enclosure)
CableEnclosureDestinationTwo as Long Interger (holds the index of an enclosure)
.......other data fileds
0
 
LVL 1

Author Comment

by:rabroersma
ID: 22851953
While this is true, I am a big fan of natural keys.  I don't want to have this question to get off topic.  But I can achieve anything with a natural key design that I can do with a surrogate key design.  However, the converse of this is not true.  Using a natural key design I can impose data validation constraints using ordiary DDL statements that are not possible using a surrogate key design.

My opinion is that the table constraints should be the final safety net that are in place to ensure that all applications based upon them _cannot_ violate business rules.  Using Natural Key designs allow me to do this and this is why I like to use them.

But in either case, using a surrogate key will not help with my problem.
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 22852670
You could establish a special constraint table that contains only CableID and CountOfEnclosureID. This table would be incremented and decremented upon stored procedures for insert or delete events. In the afterinsert event, if the count is greater than 2, you would raise a dialog and rollback the event.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:rabroersma
ID: 22853272
That is an interesting idea, I hadn't ever considered doing this.

You mentioned that stored procedures could be used.   Do you have a documentation links that describe how to build these in Access?

Also, would these procedures need to be called from a form, or is there away in Access to implement as a trigger on affected tables? (Does Access support triggers when implemented from ADO?)
0
 
LVL 10

Accepted Solution

by:
LennyGray earned 500 total points
ID: 22853400
The stored procedure would reside in your sqlServer table on the field level.

If you do not want to do it in SqlServer (which is the best place), then you can place the logic on the forms that add or delete data.

Calling a store procedure from Access is managed here:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22741094.html?sfQueryTermInfo=1+access+call+procedur+store

or

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22674876.html?sfQueryTermInfo=1+access+call+procedur+store

or

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_22636094.html?sfQueryTermInfo=1+access+call+procedur+store

You can also go to Experts Exchange .... Home, Find Your Solution, Search The Knowledge Base and type in "access sqlserver stored procedures" (without quotes) and you will find many solutions.

Access does not support triggers. Events are the customary way to emulate triggers. You could use the BeforeInsert event on the form to check if the Constraint Table count goes over 2 and raise the dialog there. You can use the OnInsert event to increment the table and OnDelete to decrement the table.

If you use these events, remember that if anyone circumvents the form and directly enters data into the tables, the constraints that you are designing are vulnerable. Putting the constraint in SQLServer on the record level stops even data entries that circumvent the form.


Good Luck!!!
Lenny Gray

0
 
LVL 1

Author Closing Comment

by:rabroersma
ID: 31512103
While the answer works. I would still like to know how to correctly implement table sub-select check constraints in MS Access tables.  The SQL Standard defines this functionality and MS-Access via ADO says that It can implement it.  However I have yet to see it successfully work.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now