February 10, 2022 at 12:41 pm
Hello Experts
I have defined a table with an incrementing primary key (pretty usual), and also with a unique index on two other fields, thus:
Create Table dbo.CTRs (
CTRseqNoIntegeridentity(1,1) not null, -- Incrementing number, generated on import.
LocalPatientIDvarchar(20) not null, -- Assigned by provider, unique to them
ReviewDateDate not null,-- Date of CTR
SourceFilevarchar(255) not null,-- Name of source CTR template file
IsInpatientYNvarchar(255) null-- Y or N
-- ,other fields....
constraint PK_CTRseqNo primary key clustered
(CTRseqNo asc),
index IX_Pt_RevDate unique nonclustered
(LocalPatientID asc,
ReviewDate asc)
);
go
I now want to define another table which has a multi-field foreign key back to the first table, something like this:
Create table CTR_PanelMembers (
RecordIDInteger identity(1,1) not null,-- purely to avoid heapness
CTRseqNoInteger not null,-- related CTR
LocalPatientIDvarchar(20) not null,-- From related CTRs record. Not strictly needed but should help with indexing
ReviewDateDate not null,-- From related CTRs record. Not strictly needed but should help with indexing
PanelMemberNamevarchar(255) not null,-- Panel member name
PanelMemberTypevarchar(30) not null -- E.g., Chair, Commissioner Representative, Advocate.
constraint PK_RecordID primary key nonclustered
(RecordID asc),
-- this bit gives error message, see below
constraint FK_CTR_PM_CTRs2 foreign key (LocalPatientID, ReviewDate) references dbo.CTRs(LocalPatientID, ReviewDate)
on delete cascade
on update cascade,
index IX_CTR_PM_Pt_RevDate nonclustered -- won't be unique
(LocalPatientID asc,
ReviewDate asc)
);
go
In other words, I want to ensure that any correction of the LocalPatientID or the ReviewDate field in the parent CTRs table gets reflected in the CTR_PanelMembers table. Since the fields are part of a unique index in the parent table, surely this should be allowed? However, when I try to run the create on the second table, I get the message: There are no primary or candidate keys in the referenced table 'dbo.CTRs' that match the referencing column list in the foreign key 'FK_CTR_PM_CTRs2'.
What am I not doing right? Thanks in advance.
MarkD
February 10, 2022 at 1:31 pm
Trimmed down your code a bit. Works for me.
DROP TABLE IF EXISTS dbo.CTR_PanelMembers;
DROP TABLE IF EXISTS dbo.CTRs;
CREATE TABLE dbo.CTRs
(
CTRseqNo INTEGER IDENTITY(1, 1) NOT NULL -- Incrementing number, generated on import.
,LocalPatientID VARCHAR(20) NOT NULL -- Assigned by provider, unique to them
,ReviewDate DATE NOT NULL -- Date of CTR
,CONSTRAINT PK_CTRseqNo
PRIMARY KEY CLUSTERED (CTRseqNo ASC)
,INDEX IX_Pt_RevDate UNIQUE NONCLUSTERED (LocalPatientID ASC, ReviewDate ASC)
);
GO
CREATE TABLE dbo.CTR_PanelMembers
(
RecordID INTEGER IDENTITY(1, 1) NOT NULL -- purely to avoid heapness
,CTRseqNo INTEGER NOT NULL -- related CTR
,LocalPatientID VARCHAR(20) NOT NULL -- From related CTRs record. Not strictly needed but should help with indexing
,ReviewDate DATE NOT NULL -- From related CTRs record. Not strictly needed but should help with indexing
,CONSTRAINT PK_RecordID
PRIMARY KEY NONCLUSTERED (RecordID ASC)
-- this bit gives error message, see below
,CONSTRAINT FK_CTR_PM_CTRs2
FOREIGN KEY (
LocalPatientID
,ReviewDate
)
REFERENCES dbo.CTRs (
LocalPatientID
,ReviewDate
) ON DELETE CASCADE ON UPDATE CASCADE
,INDEX IX_CTR_PM_Pt_RevDate NONCLUSTERED -- won't be unique
(LocalPatientID ASC, ReviewDate ASC)
);
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 10, 2022 at 2:17 pm
Your second table, CTR_PanelMembers, is defined as a heap.
Instead, it should have a clustered index on:
( CTRseqNo, RecordID )
I don't see anything wrong with the FK definition in the second table. Verify that the CTRs has been properly created.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 10, 2022 at 2:54 pm
I ran Phil's code, and it worked perfectly. As far as I can tell, it is equivalent to what I wrote.
I then re-executed my original code - which also worked perfectly! Like Phil, I dropped the old versions before creating them.
Oddly, though, the name of the parent table in the references clause of the FK constraint still has a squiggly red line under it, with a message like the one I posted above. SSMS bug I suppose. Parsing T-SQL with all its convoluted syntax must be a nightmare.
And you're right Scott of course. The table would have been a heap. Corrected.
Many thanks guys.
MarkD
February 10, 2022 at 2:54 pm
I guess my first question is why are you duplicating three columns from the CTRs table into CTR_PanelMembers table?
To me, you only need the PK from the CTR table in the CTR_PanelMembers table.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 10, 2022 at 3:05 pm
Yes Michael, correct. You're right that I could join on the CTRseqNo field and pull the patient ID and review date out of the parent CTRs table.
My thought behind duplicating the fields in the child table (or tables actually: there are going to be several more) was to enable subsequent querying without having to join to the parent table if those two fields were the only thing I wanted from it. On balance, I don't actually know if this is a good idea or not.
Very happy to hear your thoughts on this.
MarkD
February 10, 2022 at 3:24 pm
Yes Michael, correct. You're right that I could join on the CTRseqNo field and pull the patient ID and review date out of the parent CTRs table.
My thought behind duplicating the fields in the child table (or tables actually: there are going to be several more) was to enable subsequent querying without having to join to the parent table if those two fields were the only thing I wanted from it. On balance, I don't actually know if this is a good idea or not.
Very happy to hear your thoughts on this.
MarkD
There seems to be a generic attitude that more joins is bad, less joins is better. This is a regular war I have with the developers. Yes, selective denormalization is a good thing. It depends!
However, a structure that is normalized, and indexed properly, will typically perform better than a denormalized structure. Making a recommendation without more details is impossible, however.
In one sentence, create a normalized structure, and when there are situations that arise, denormalize as needed.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 10, 2022 at 6:16 pm
Thanks Michael, fair enough.
I'm going to put the extra fields in to start with, but I may rip them out if testing indicates it. At this early stage I can't tell which is better as I am still collecting data. Fortunately, I have total control over the structure for the time being.
MarkD
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply