Dual primary keys a problem in SQL?

E

el zorro

I have an Access database that I am converting from mdb to adp, with a SQL
Server the back-end.

I have 2 tables (Table A, Table B) in an Acccess mdb that are related by
primary keys in each table. THe slightly different aspect of this
relationship is that one of the tables has dual primary key fields (i.e., the
combination of the 2 fields may not repeat). SO Table A has "key 1" which is
related to "Key 1" in Table B. Table B also has "key 2" that is not part of
the relationship with Table A.

Access has been fine with this, but when I "upsize" the back end, the wizard
does not like the relationship, possibly because there are (correctly) many
records in Table A that have no corresponding records in Table B, or maybe
because there are duplicate instances of Table B's key 1, which is also ok
because it's only the combination if Key 1 + Key 2 that is unique in Table B.
But I don't know.

I have deleted this relationship in the mdb file and the upsizing was
succesful. But when I try to put the relationship back via the "Diagram"
feature in SQL Enterprise Manager, it won't work unless I instruct it to not
check the existing data. This makes me nervous-- the existing data is good.

So, my question is-- What's going on here? Does SQL have problems with dual
primary keys in a table?
 
S

Sylvain Lafontaine

Shouldn't be a problem for SQL-Server but ADP might have problem with them.
I don't recommend to use a dual primary key with ADP.

In your case, launch a query with a Group By and an Having in order to
etablish if there are any duplicate.
 
P

Paul Shapiro

SQL Server works fine with multi-attribute PK's, but it definitely insists
that primary and foreign keys obey the rules. If it refuses to create the
relationship when checking is enforced, something is probably wrong. What
error message does it give?

Which table is the parent? If A is the parent table, with a single PK
attribute, and B is the child table with a 2-attribute PK, that should be
fine. If B is the parent table, then it's an invalid relationship. The FK
has to be the complete PK, not part of it.

If you provide the sql DDL statements which create the two tables and the
relationship, you could get more particular advice.
 
A

a a r o n _ k e m p f

yah I've never had a problem with composite keys.. I use them
sometimes-- probably once per month, I would guess.. Almost everything
that I'd do like that would have a surrogate key.

Are you having a problem with merely 'editing records' or can you give
us some more guidance?
 
M

MISHO

HI


þþßÊÈ "Paul Shapiro said:
SQL Server works fine with multi-attribute PK's, but it definitely insists
that primary and foreign keys obey the rules. If it refuses to create the
relationship when checking is enforced, something is probably wrong. What
error message does it give?

Which table is the parent? If A is the parent table, with a single PK
attribute, and B is the child table with a 2-attribute PK, that should be
fine. If B is the parent table, then it's an invalid relationship. The FK
has to be the complete PK, not part of it.

If you provide the sql DDL statements which create the two tables and the
relationship, you could get more particular advice.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Should a table must have a primary key ? 0
SQL Server 2000 insert primary key in import 1
SQL Statement 3
2 primary keys 3
Primary and Foreign Keys 8
Relationships and Keys 18
referential integrity and tables 4
Northwind Primary Keys (Access 2003) 9

Top