-1

I have table A and B which cannot be edited and both using composite primary keys (region_id, number).

I have N tables, so called Information, each has its own ID as primary key.

A(or B) <-> Info Tables are M:N relationship and I need such a table. So I designed a table CtoInfo (where C is either A or B) with these columns

CREATE TABLE CtoInfo (
region_id ..
c_number ..
c_type // either A or B

info_id 
info_type
.. //some other columns
)

first 3 columns identify A or B, other 2 columns, the info. (type says which table and id is the PK)

Now I want to make a Primary key on this table. But it looks like I need to include 5 columns in that PK constraint !?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
theSpyCry
  • 12,073
  • 28
  • 96
  • 152
  • 1
    5 columns as a PK is not a problem. Your problem is going to be declaring the FK. You cannot mix tables in a single relationship (FK). You can have multiple relationships point to different FK. But within a FK one table. You need an AB master that the A, B, and the other tables reference. With proper 3nf pretty sure you should not have keys spanning tables. There is not a column datatype of table. – paparazzo Aug 19 '13 at 15:57
  • If I understood correctly, I should not make a common M:N Table where M comes from 2 different tables? I should rather have 2 M:N tables ? – theSpyCry Aug 19 '13 at 16:00
  • I guess you could do this with two M:N tables but I think you still have a problem with declaring the FK. If table Z only needs a relationship to one of M:N then fine. If table V has a mixed relationship then all you can do is declare both FK and in that case both FK are enforced. – paparazzo Aug 19 '13 at 16:04
  • and what about to use a trigger instead of a foreign key constraint? i know it would be a performance loss – theSpyCry Aug 19 '13 at 16:19
  • Did you try a trigger? Just how to you expect to dynamically connect to different tables in a trigger? What have you tried? – paparazzo Aug 19 '13 at 16:37
  • i would check if that composite PK exists in one of that tables. In an instead of IU trigger – theSpyCry Aug 19 '13 at 16:39
  • If that is the solution then post it as the answer or delete the question. Fixing the data model would be way too much trouble. – paparazzo Aug 19 '13 at 16:51

1 Answers1

1

SQL Server allows up to 16 columns to be included in a constraint. So five columns is not a problem.

When working with natural keys, you often get into multi-column primary key constraints. A "link" table with 5 or 6 columns as the key is not uncommon in such a design.

Now, there are a few problems with your design. In general you want to keep the primary key as short as possible for performance reasons. A single integer surrogate key is often the better choice from a performance perspective.

You also are defining a foreign key relationship that you cannot enforce. This usually points to a problem in the database schema. You are linking to 2 different entities from the same column. That means that they represent something very similar and should probably live in the same table. Then you can provide additional tables that contain the information that is specific to one of them but not the other. sys.objects, sys.tables and sys.procedures give you an example of how that could work. (In this case sys.tables and sys.procedures are views that also include columns form sys.objects. In your case you would not need to repeat that information.)

Sebastian Meine
  • 11,260
  • 29
  • 41
  • ok and the same problem with the Info tables ? I am referencing N Info tables (each has another set of columns) based on the info_type in the M:N table – theSpyCry Aug 19 '13 at 16:05
  • what about instead of insert / update triggers? in place of a foreign key constraint. I cannot join those 2 tables in one and I have about 10 Info tables. I do not want to have A2I1, A2I2 etc along with B2I1, B2I2 as separate tables – theSpyCry Aug 19 '13 at 16:38
  • With you current design you cannot use declarative foreign keys. That points to a problem in the database layout. There are many ways to "deal" with that like the trigger method you mentioned, but all those are just a bandage over the symptom that don't address the underlying problem. The only advise I can give you is to fix the design. -- Your original question however was about primary key constraints, and I believe I answered that. Let me know if you need more information about that. – Sebastian Meine Aug 19 '13 at 18:28