limit number of records with a linked table

[aft3rgl0w]

hey all, having trouble figuring this one out... i have 2 tables, one is a
waybill we use that has all the customer info. for each of these records,
there is a section for filling in the items being sent, of which i am
allowing 8 (this is the number that will physically fit on the waybill).
there is a table linked to the first which contains this info. i am using a
primary key ID on the first table, and this is my link field to the second
table. on the form for this, i have a subform which contains the linked
table data. this all is working fine. what i want is to only allow 8 items
to be entered per record. the window has been sized to fit exactly 8
records... how can i prevent more than that being entered?

 

[Bob Hairgrove]

hey all, having trouble figuring this one out... i have 2 tables, one isa
waybill we use that has all the customer info. for each of these records,
there is a section for filling in the items being sent, of which i am
allowing 8 (this is the number that will physically fit on the waybill).
there is a table linked to the first which contains this info. i am using a
primary key ID on the first table, and this is my link field to the second
table. on the form for this, i have a subform which contains the linked
table data. this all is working fine. what i want is to only allow 8 items
to be entered per record. the window has been sized to fit exactly 8
records... how can i prevent more than that being entered?

It can't be done easily in a linked table because usually, the primary
key of a linked table will consist of a combination of fields
including the linked field whose value might be duplicated, and not a
single autoumber field incrementing sequentially. If you had just an
autonumber field, you could put a check constraint on it (i.e. a
validation rule in Access-speak) such as:
" < 9".
However, even that isn't 100% bullet-proof since a new row can be
inserted into a table with 0 or some negative value into the
autonumber field by using an SQL "insert into" statement.

What you can do is to implement the application so that the only way
to update the table is through a form. Then you can use the form's
BeforeInsert event (if it is a bound form) and cancel the insert if
there are already 8 rows or more for the criteria you have. Use the
DCount() statement for this.

If the form is unbound, you will probably have a command button to
perform the data inserts, so you can run the same code in the OnClick
event procedure.

 

[Bob Hairgrove]

If you had just an
autonumber field, you could put a check constraint on it (i.e. a
validation rule in Access-speak) such as:
" < 9".
However, even that isn't 100% bullet-proof since a new row can be
inserted into a table with 0 or some negative value into the
autonumber field by using an SQL "insert into" statement.

Duh ... just use this instead:

"Between 1 and 8"

 

[Marshall Barton]

hey all, having trouble figuring this one out... i have 2 tables, one is a
waybill we use that has all the customer info. for each of these records,
there is a section for filling in the items being sent, of which i am
allowing 8 (this is the number that will physically fit on the waybill).
there is a table linked to the first which contains this info. i am using a
primary key ID on the first table, and this is my link field to the second
table. on the form for this, i have a subform which contains the linked
table data. this all is working fine. what i want is to only allow 8 items
to be entered per record. the window has been sized to fit exactly 8
records... how can i prevent more than that being entered?

Use this line of code in the subform's Load, AfterInsert and
AfterDelConfirm event procedures:

Me.AllowAdditions = (Me.Recordset.RecordCount < 8)

 

[Bob Hairgrove]

you can use the form's
BeforeInsert event

This should be the *subform's* BeforeInsert event.

 

[Bob Hairgrove]

If you are advocating CHECK constraint usage, and good for you, then
you can do it properly e.g.

CHECK(9 >= (SELECT COUNT(*) FROM ThisTable AS T2))

Jamie.

I have only used Access versions 2, 97 and 2000. Since when did Access
start supporting check constraints in the DDL? It's really a pleasant
surprise, if it is true.

And if Access supports "select count(*) ..." within the constraint, it
is already a lot further along than DB2 or Oracle (now THAT would
surprise me!)

Even nicer would be the news that Access supported triggers...

 

[Bob Hairgrove]

Here's a question for you: if Jet did have triggers but Access lacked
the UI to create/maintain them, would you still use them?

By "UI" I assume you mean "graphical user interface"? Most of my
database work I do without any graphical user interface. I only use
Access for a few things, anyway.

Of course I would still use them! I use them in other SQL databases
without a graphical UI (don't you?)

 

[Bob Hairgrove]

BTW you may be interested in this thread:

http://groups-beta.google.com/group..._frm/thread/2e6ee72fd3ce3967/41271b75c7d9fe31

OK, so they are broken. What else is new? Are they still broken in the
most current versions?

 

[Granny Spitz via AccessMonster.com]

And if Access supports "select count(*) ..." within the constraint, it
is already a lot further along than DB2 or Oracle (now THAT would
surprise me!)

You make it sound like Oracle is so primitive it can't support aggregate
functions for check constraints. Technically it does, but the syntax isn't
"CHECK(SELECT COUNT(*) ..." like you'd want it to be. You need to use an
equivalent (an alias) like this:

ORA> CREATE MATERIALIZED VIEW ViewA
2 REFRESH FAST ON COMMIT
3 AS
4 SELECT ForeignKey, COUNT(*) AS Cnt
5 FROM TableA
6 GROUP BY ForeignKey
7 /

Materialized view created.

ORA> ALTER TABLE ViewA
2 ADD CONSTRAINT Check_Max8Rows CHECK(Cnt<=8)
3 /

Table altered.

As you can see, Cnt is the equivalent of COUNT(*) of each ForeignKey value in
the table, so this check constraint will allow no more than 8 rows for each
ForeignKey value.

 

[Bob Hairgrove]

You make it sound like Oracle is so primitive it can't support aggregate
functions for check constraints. Technically it does, but the syntax isn't
"CHECK(SELECT COUNT(*) ..." like you'd want it to be. You need to use an
equivalent (an alias) like this:

ORA> CREATE MATERIALIZED VIEW ViewA
2 REFRESH FAST ON COMMIT
3 AS
4 SELECT ForeignKey, COUNT(*) AS Cnt
5 FROM TableA
6 GROUP BY ForeignKey
7 /

Materialized view created.

ORA> ALTER TABLE ViewA
2 ADD CONSTRAINT Check_Max8Rows CHECK(Cnt<=8)
3 /

Table altered.

As you can see, Cnt is the equivalent of COUNT(*) of each ForeignKey value in
the table, so this check constraint will allow no more than 8 rows for each
ForeignKey value.

Which version of Oracle is this?

Does this mean that DML must be performed on the view in order for the
check constraint to be enforced? Updates, inserts and deletes on a
view generally should be avoided for performance reasons unless you
have fairly small tables to worry about. After a few million rows and
joining multiple tables, things can get pretty slow.

 

[Granny Spitz via AccessMonster.com]

Which version of Oracle is this?

Oracle 9i R2. Which version are you using that it didn't work for you? A
version from the '80s?

Does this mean that DML must be performed on the view in order for the
check constraint to be enforced?

No, the record update, insert or delete can be made on the table and the
check constraint will still be executed unless the constraint is deferred
beforehand.

Updates, inserts and deletes on a
view generally should be avoided for performance reasons unless you
have fairly small tables to worry about.

Hon, whoever's been telling you this has a head filled with sawdust. Oracle
is *made* for performance and can easily handle terabytes of data *very*
quickly, even in views. The only Oracle databases I've ever seen that had
performance problems had tables built like this:

1) No primary key
2) 87 columns
3) 126 indexes
4) 12 records
5) 23 foreign key constraints, all of which had 8 or more columns for each
foreign key/unique key combination

And some even had cascading triggers, but that was just icing on the cake
because the real problem was the people who designed these messes were
getting paid to build more messes.

After a few million rows and
joining multiple tables, things can get pretty slow.

If your views are slow, the DBA didn't design the schemas and tablespaces
correctly. Even if there are millions of rows in each table, in a cluster
index data from the multiple tables in the view are stored in the same data
block, meaning one disk I/O to retrieve records in an Oracle view with 4
joined tables would take 4 or more disk I/Os to retrieve the same records in
a database server that doesn't support this feature, like SQL Server, because
data in different tables is stored in different data blocks in SQL Server.
Oracle is built for performance and reliability and does both *extremely*
well, but even the high end database engines with the fastest hardware can't
completely overcome poorly designed databases and views. If you're
complaining Oracle is slow, something is really, really wrong.

 

[Bob Hairgrove]

Oracle 9i R2. Which version are you using that it didn't work for you? A
version from the '80s?

The last one I have used extensively was 8i. I know that a lot has
changed since then (for the better!). I believe that 8i was released
in the early 90's.

No, the record update, insert or delete can be made on the table and the
check constraint will still be executed unless the constraint is deferred
beforehand.

This seems like a recipe for unpleasant surprises when one developer
creates a view with a check constraint and another developer writing
procedures knows nothing about the view and its new constraints. IMHO,
views shouldn't have this kind of impact on the database design.

Hon, whoever's been telling you this has a head filled with sawdust. Oracle
is *made* for performance and can easily handle terabytes of data *very*
quickly, even in views.

Thinking back to the case in mind, it was a view that used "instead
of" triggers to perform DML on the underlying tables which were not
directly updatable due to security concerns. Some of the client
applications needed to do it this way; the stored procedures I was
writing should have been using an API implemented in PL/SQL by the
DBA's, but some of my code written early in the testing phase used the
views and somehow never got converted until everything was already in
production. Fortunately, these were batch jobs that only ran at night,
so I had time to correct it during the day. Wasn't really a major
issue ... it was actually running fast enough until additional indexes
had to be added for some of the client applications. Then there was a
noticeable difference between using the view and using the API.

If your views are slow, the DBA didn't design the schemas and tablespaces
correctly. Even if there are millions of rows in each table, in a cluster
index data from the multiple tables in the view are stored in the same data
block, meaning one disk I/O to retrieve records in an Oracle view with 4
joined tables would take 4 or more disk I/Os to retrieve the same records in
a database server that doesn't support this feature, like SQL Server, because
data in different tables is stored in different data blocks in SQL Server.
Oracle is built for performance and reliability and does both *extremely*
well, but even the high end database engines with the fastest hardware can't
completely overcome poorly designed databases and views. If you're
complaining Oracle is slow, something is really, really wrong.

I'm not complaining that Oracle is slow ... far from it! And what you
say about proper design is so true. However, it isn't always that
simple to design correctly, and requirements will change during the
lifetime of an application. For example, tables need to be filled with
data from external files. Do you want indexes? No ... only the bare
minimum of constraints to ensure RI. Later on in the game, the same
tables have millions of rows, and clients with views start complaining
that things are getting slow. So they add an index or two. But the
tables are still being filled with data! Now the developers of the
import routines complain that THEIR apps are slow as molasses, so the
DBA must tune the queries and adjust the indexes. Also, the query
engine migh suddenly decide to use a different index depending on the
cardinality of the data which can also change things drastically.
Hints need to be added to the SQL so that the correct indexes are used
.... tablespaces can get fragmented ... hard disks can fail ... it goes
on and on.

One thing I did learn during my Oracle experience is that there is a
reason why Oracle DBA's and senior developers get paid as much as they
do (if they are any good at it, that is).

 

[Granny Spitz via AccessMonster.com]

This seems like a recipe for unpleasant surprises when one developer
creates a view with a check constraint and another developer writing
procedures knows nothing about the view and its new constraints.

I've seen so many developers who think they're gods, and the rest of us
should worship them so *they* shouldn't have to coordinate with *us.* I have
no sympathy for these developers who get this unpleasant surprise when they
don't coordinate with the other developers on the team *and* don't check the
system tables for constraints. These developers need to find a new job as a
politician, a garden slug, a weather vane rooster, or some other occupation
that doesn't require them to use the brains God gave them.

IMHO,
views shouldn't have this kind of impact on the database design.

Hon, don't be fooled by the syntax. That constraint is on the *table*
because nobody needs to ever use that view in SQL, but they'll still be
halted when attempting to add a 9th record to this table (or another view on
this table) with the same foreign key value. If you don't believe me look at
the SQL Plus response to the command that adds the constraint: "Table
altered," not "View altered."

Thinking back to the case in mind, it was a view that used "instead
of" triggers to perform DML on the underlying tables which were not
directly updatable due to security concerns.

Instead of triggers mean "don't update/add/delete this record, but run *this*
code instead." *This* code (whatever it is) can be *much* slower when using
cursors on a view consisting of multiple tables rather than a set operation
on an individual table, so both the efficiency of the view *and* the code
should be examined.

For example, tables need to be filled with
data from external files. Do you want indexes? No ... only the bare
minimum of constraints to ensure RI. Later on in the game, the same
tables have millions of rows, and clients with views start complaining
that things are getting slow. So they add an index or two. But the
tables are still being filled with data! Now the developers of the
import routines complain that THEIR apps are slow as molasses, so the
DBA must tune the queries and adjust the indexes.

The smart DBAs use separate tablespaces for data and indexes and store the
data tablespace on a separate disk than the index tablespace. Usually dual
CPUs too, but that's not always possible. Before importing large amounts of
data, the table is locked (to keep users out), indexes are dropped, data is
imported, indexes are rebuilt, and the table unlocked again. Most of the
time it happens so fast, even with 100,000s of records, the users don't
notice a delay due to the table being locked for a few seconds. You *never*
hear about slow apps when you have smart DBAs, smart system admins and smart
developers who work together. It's unfortunate this combination is rare.

Hints need to be added to the SQL so that the correct indexes are used

That's what query hints are for. The developer gets some manual control over
the process when necessary.

... tablespaces can get fragmented

Smart DBAs use uniform sized segments, manage segments manually instead of
accepting all the defaults, and coalesce periodically to reduce fragmentation
to a minimum.

... hard disks can fail

That's why smart DBAs use failover clusters with RAID. It doesn't matter if
one or more disks fail. They're redundant.