February 26, 2009 at 12:50 pm
I would appreciate any help I can get on an issue I am having. I have simplified the table structures. I have a table that contains home listings for several different companies. The table is expected to grow to an enormous size fairly quickly. I am paritioning the listings table by company to help speed up some of the queries since no queries cross companies. The problem I am having is when I try to create a foreign key reference on AuditListings I receive an error stating there are no primary or candidate keys in the referenced table 'dbo.Listings' that match the referencing column list in the foreign key. I know that I could add CompanyID to the AuditListings table and partition it also but it is going to be a relatively small table and there is no need to partition it. Can I create this foreign key?
create table dbo.Listings
(
ID bigint not null identity (1, 1),
CompanyID int not null,
ListingNumber int not null,
constraint PK_Listings primary key clustered (ID, CompanyID)
) on PartitionScheme (CompanyID)
create table dbo.AuditListings
(
ID bigint not null identity (1, 1),
ListingID bigint not null constraint FK_AuditListings_ListingID
foreign key (ListingID) references dbo.Listings (ID),
constraint PK_AuditListings primary key clustered (ID)
) on [PRIMARY]
February 27, 2009 at 6:26 am
I'm not real familiar with Partitioning, but I think you can add CompanyId to AuditListings to use as a foreign key relationship without having to partition the table.
Your other option is to create a unique index on ID, then you can create the foreign key against it. Since it is an identity value it will be unique.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 27, 2009 at 6:52 am
I do not think the issue has anything to do with partitioning but with the concept of referential integrity.
I would start by asking myself the question: "Which parent-child relationship do I want to enforce?" ... then you just do it taking into consideration the parent must be unique meaning a single row in the table e.g. PK or Unique Index.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 27, 2009 at 7:03 am
I originally thought about making Listings.CompanyID aclustered index and Listings.ID a unique nonclustered primary key. However, it would not let me partition the unique nonclustered primary key. Additionally, I really didn't think Listings.CompanyID as a clustered index would be a good candidate by itself. There are going to be millions of rows per company.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply