1

When our IT department converts Access databases to SQL Server the relationships do not transfer over. In the past, I have provided ERDs that they can use to build the relationships. In this case, I didn't.

What are the possible consequences of defining the table relationships in the MS Access Front End versus on the SQL Server itself?

It would be ideal if I could just create the relationships in Access and avoid submitting a request to IT, but I don't want to risk performance issues now or in the future.

CiViCChiC79
  • 31
  • 5
  • The consequences are, as you describe, that IT ignores the relationships in Access. We cannot bring you in control of this ignorance, so what is your question really? – Gustav Sep 10 '16 at 07:35

1 Answers1

2

There may be some misconceptions.

A relationship in SQL Server enforces referential integrity (an order cannot have a customer ID that doesn't exist). It does not automatically create an index on the Foreign Key, so it has per se no impact on performance.

But in most cases it is a good idea to define an index on a foreign key, to improve performance.

A relationship that you define in Access on linked tables does neither. It cannot enforce referential integrity (that's the server's job).
It is merely a "hint" that the tables are related via the specified fields, e.g., so that the Query Builder can automatically join the tables if they are added to the query design. (copied from here)

So you should

  • Create the relationships in SQL Server to avoid inconsistent data. ("But my application logic prevents that!", I hear you say. Well, applications have bugs.)
  • Create indexes on foreign keys where appropriate to avoid performance problems.
  • If you are working with queries in the Access frontend, additionally define the relationships there.

Ideally you should have a test server where you can yourself define the relationships, and just send the finished SQL script to IT.

Community
  • 1
  • 1
Andre
  • 26,751
  • 7
  • 36
  • 80
  • Thanks Andre! I will go ahead and submit an ERD to the IT department to get the relationships added to SQL. – CiViCChiC79 Sep 12 '16 at 13:20
  • PS - We do have a staging environment, but no one outside of IT is currently allow to have SQL Mgmt Studio or server access. – CiViCChiC79 Sep 12 '16 at 13:21
  • Ok. I don't know your role in all this, but if you are (or will be) developing in this area, you are going to need both... – Andre Sep 12 '16 at 13:32