Re: How do you mulitply in a field?

Tech-Archive recommends: Fix windows errors by optimizing your registry

"Jamie Collins" <jamiecollins@xxxxxxxxxx> wrote in message
news:1187708262.026059.223180@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On 21 Aug, 13:43, "BruceM" <bam...@xxxxxxxxxxxxxxxx> wrote:
I have
asked how one places a unique constraint on a combination of fields
without
making them into an Access PK. I don't know much about using a database
engine other than Jet, so the differences with a SQL engine are lost on
me.
If you have answered my question about multi-field keys, I could not
understand your response as an answer to the question.

ALTER TABLE EmployeeSalariesHistory ADD
CONSTRAINT my_constraint_name
UNIQUE (employee_number, start_date);

This will work for Jet in ANSI-92 Query Mode Jet SQL. It also happens
to be standard SQL so has a very good chance of working in any given
SQL DBMS. You can do the same in the Access user interface by create
an index, choosing the fields and specifying 'allow duplicates =
no' (or similar; haven't got Access on this machine).

OK, thanks. That led me on an investigation I might not have made
otherwise.

if someone is unaware of Access/Jet's
clustering behaviour, can they really make an informed decision about
PRIMARY KEY designation?

No idea. I know that my databases work, and it's not just dumb luck.

An mdb doesn't need indexes to work but they can improve performance.
Why miss out on the opportunity of a physically ordered index?

Who said I avoid indexes? I have used them on single fields only, but I can
see the value of indexing on a combination of fields (although maybe not
always a unique combination, for instance with FirstName and LastName
fields).


BTW when I say 'indexes' I mean the 'allow duplicates = yes' kind i.e.
the ones for performance and nothing else.

I prevent it by limiting the choices the user can make once SUV has been
selected.

I open up Excel, create a new MSQuery session, connect to your mdb,
and execute this from it's SQL window:

INSERT INTO SUVs (VIN, etc)
SELECT VIN, etc)
FROM Vehicles
WHERE vehicle_type = 'Sedan';

How has your application 'limited my choices'? I now have 'sedans' in
my SUVs table!

I apply security so that the database can't be hacked.

What's your position on PRIMARY KEY? If I showed you this table:

CREATE TABLE Employees
(
employee_number INTEGER
);

and pointed out that all nine front end programs known to use the
database have front end code to trap duplicates and NULLs, would you
think it was a good idea to omit a NOT NULL unique constraint from the
SQL table?

I *never, ever* argued in favor of allowing nulls in required

I think you missed my point.

You said earlier you would trust your front end controls to prevent
'sedans' from being entered into the 'SUVs' tables. Because I consider
this unacceptable myself (data integrity being a job for the data
layer IMO), I was wondering whether you think a simple unique
constraint (or PK if you like) can also be trusted to the front end
application or nine front end applications in this case. What if one
of those applications had a bug in their duplicate testing routine?
Personally, I'd want a constraint in the DBMS to trap such 'data'
bugs.

When I select an Employee in one of those nine databases, the number only
gets stored. If I am selecting only one name (number), the duplicates issue
does not occur. If I am selecting subform records (attendees at a training
session or something) then I can see the point to a multi-field index in the
child table, consisting of the EmployeeID together with the table's key
field. However, I don't want it ever to get to the point of the user seeing
the built-in error message that results from accidentally selecting the same
name twice.

I'm glad finally to understand the distinction between multi-field index and
multi-field PK.


.

Relevant Pages

  • Help!!! Merge Replication and identity values
    ... until there was a database schema change and I had to rebuild the ... publication and re-initialize all subscribers. ... the distribution..MSrepl_identity_range using the sql script. ... * In Check Constraint tab of the replicated table (generating identity ...
    (microsoft.public.sqlserver.replication)
  • Help!!! Merge Replication and identity values
    ... until there was a database schema change and I had to rebuild the ... publication and re-initialize all subscribers. ... the distribution..MSrepl_identity_range using the sql script. ... * In Check Constraint tab of the replicated table (generating identity ...
    (microsoft.public.sqlserver.ce)
  • Re: How do you mulitply in a field?
    ... asked how one places a unique constraint on a combination of fields without ... I don't know much about using a database ... This will work for Jet in ANSI-92 Query Mode Jet SQL. ...
    (microsoft.public.access.tablesdbdesign)
  • How to Find Out Check Columns?
    ... I'm working on a database having default constraint names created by SQL ... Server 2000. ...
    (microsoft.public.sqlserver.programming)
  • Re: CREATE AGGREGATE failed because type Concatenate does not conform to UDAGG specification due to
    ... Go to the Database tab and click on the browse button next to the connection string. ... In the New Database Reference dialog, enter the details for the database where you want to deploy the assembly and create the user defined aggregate. ... I'm trying to do some CLR integration with sql server 2005. ...
    (microsoft.public.sqlserver.programming)