Attempted foreign key constraint creation involving multiple fields fails

Question

Attempted foreign key constraint creation involving multiple fields fails

Viewing 7 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247183 More actions · Answer 1

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)
);

ScottPletcher SSC Guru Points: 101207 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Mark Dalley Hall of Fame Points: 3009 More actions · Answer 3

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

Michael L John One Orange Chip Points: 27079 More actions · Answer 4

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/

Mark Dalley Hall of Fame Points: 3009 More actions · Answer 5

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

Michael L John One Orange Chip Points: 27079 More actions · Answer 6

Mark Dalley wrote:

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/

Mark Dalley Hall of Fame Points: 3009 More actions · Answer 7

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