Solved

Access Table Constraints Built using ADO

Posted on 2008-10-31
6
438 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing 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 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

776 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