?
Solved

Access Table Constraints Built using ADO

Posted on 2008-10-31
6
Medium Priority
?
445 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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 1500 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

801 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