locked
referencing foreign keys from composite primary keys RRS feed

  • Question

  • I'm trying to create a column(originalward)  with a foreign key referencing composite primary key in a table (facilities.warddetails)

    create table Patients.medicalhistory
    (RecordID int IDENTITY (1,1),
    Disease Varchar(100) NOT NULL,
    Originalward varchar(10) constraint Cwardname foreign key(wardID, wardname) references facilities.warddetails(wardID, wardname),
    dicshargeward varchar(10) constraint Cwardname foreign key references

    its gives two errors

    1.foreign key 'Cwardname'  references invalid column wardID in referencing table medicalhistory

    2. foreign key 'Cwardname'  references invalid column wardname in referencing table medicalhistory

    please help

    thanks in advance

    Monday, February 11, 2013 11:42 AM

Answers

  •  

    CREATE TABLE [facilities].[warddetails]
    ( [wardID] [int] NOT NULL, 
      [WardName] [varchar](20) NOT NULL,
       CONSTRAINT [PK_warddetails] PRIMARY KEY CLUSTERED ( [wardID] ASC, [WardName] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
        ON [PRIMARY]
    GO 
    	
    CREATE TABLE [patients].[medicalhistory](
    	[RecordID] [int] IDENTITY(1,1) NOT NULL,
    	[Disease] [varchar](100) NOT NULL,
    	[OriginalwardID] [int] NULL,
    	[OriginalwardName] [varchar](20) NULL, 
    	[dischargeward] [varchar](10) NULL,
     CONSTRAINT [PK_medicalhistory] PRIMARY KEY CLUSTERED 
    (
    	[RecordID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [patients].[medicalhistory]  WITH CHECK ADD  CONSTRAINT [FK_medicalhistory_warddetails] FOREIGN KEY([OriginalwardID], [OriginalwardName])
    REFERENCES [facilities].[warddetails] ([wardID], [WardName])
    GO
    
    ALTER TABLE [patients].[medicalhistory] CHECK CONSTRAINT [FK_medicalhistory_warddetails]
    GO

    I think you need to have both the columns to create foreign key constraint on composite primary key. In other words,  you should have same no of columns in both the tables to establish the foreign key constraint.
    I added OrginialWardID, OriginalWardName columns to the second table and in the foreign key constraint, I mentioned,
    [patients].[medicalhistory] (OriginalWardID,OriginalWardName) references  facilities.patients(WardID,WardName) . So, the combination of WardID,WardName is foregin key constrainted in the second table.
    I did not understand about dischargeward column??Are you trying to set up FK on that??looks like, Some script text is missing in the post.


    Hope it Helps!!




    • Edited by Stan210 Monday, February 11, 2013 9:03 PM
    • Marked as answer by Louis DavidsonMVP Tuesday, February 12, 2013 11:49 PM
    Monday, February 11, 2013 8:39 PM
  • In addition to what Stan says, you can put the entire thing in one statement, but when the FK is a composite, it can't be part of the column declaration, but instead on a different line:

    create table a
    (
     a1Id int,
     a2Id int,
     constraint pkA primary key (a1Id, a2Id)
    )

    create table b
    (
     bId  int constraint PKB primary Key,
     a1Id int,
     a2Id int,
     constraint b$references$A foreign key (a1Id,a2Id) references a (a1Id,a2Id)
    )



    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Proposed as answer by Aalamjeet Rangi Wednesday, February 13, 2013 4:11 AM
    • Marked as answer by slickk_F Tuesday, February 26, 2013 11:27 PM
    Tuesday, February 12, 2013 11:49 PM

All replies

  •  

    CREATE TABLE [facilities].[warddetails]
    ( [wardID] [int] NOT NULL, 
      [WardName] [varchar](20) NOT NULL,
       CONSTRAINT [PK_warddetails] PRIMARY KEY CLUSTERED ( [wardID] ASC, [WardName] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
        ON [PRIMARY]
    GO 
    	
    CREATE TABLE [patients].[medicalhistory](
    	[RecordID] [int] IDENTITY(1,1) NOT NULL,
    	[Disease] [varchar](100) NOT NULL,
    	[OriginalwardID] [int] NULL,
    	[OriginalwardName] [varchar](20) NULL, 
    	[dischargeward] [varchar](10) NULL,
     CONSTRAINT [PK_medicalhistory] PRIMARY KEY CLUSTERED 
    (
    	[RecordID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [patients].[medicalhistory]  WITH CHECK ADD  CONSTRAINT [FK_medicalhistory_warddetails] FOREIGN KEY([OriginalwardID], [OriginalwardName])
    REFERENCES [facilities].[warddetails] ([wardID], [WardName])
    GO
    
    ALTER TABLE [patients].[medicalhistory] CHECK CONSTRAINT [FK_medicalhistory_warddetails]
    GO

    I think you need to have both the columns to create foreign key constraint on composite primary key. In other words,  you should have same no of columns in both the tables to establish the foreign key constraint.
    I added OrginialWardID, OriginalWardName columns to the second table and in the foreign key constraint, I mentioned,
    [patients].[medicalhistory] (OriginalWardID,OriginalWardName) references  facilities.patients(WardID,WardName) . So, the combination of WardID,WardName is foregin key constrainted in the second table.
    I did not understand about dischargeward column??Are you trying to set up FK on that??looks like, Some script text is missing in the post.


    Hope it Helps!!




    • Edited by Stan210 Monday, February 11, 2013 9:03 PM
    • Marked as answer by Louis DavidsonMVP Tuesday, February 12, 2013 11:49 PM
    Monday, February 11, 2013 8:39 PM
  • thanks stan
    • Marked as answer by slickk_F Tuesday, February 12, 2013 10:01 PM
    • Unmarked as answer by Kalman Toth Wednesday, February 13, 2013 7:45 PM
    Monday, February 11, 2013 8:46 PM
  • In addition to what Stan says, you can put the entire thing in one statement, but when the FK is a composite, it can't be part of the column declaration, but instead on a different line:

    create table a
    (
     a1Id int,
     a2Id int,
     constraint pkA primary key (a1Id, a2Id)
    )

    create table b
    (
     bId  int constraint PKB primary Key,
     a1Id int,
     a2Id int,
     constraint b$references$A foreign key (a1Id,a2Id) references a (a1Id,a2Id)
    )



    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Proposed as answer by Aalamjeet Rangi Wednesday, February 13, 2013 4:11 AM
    • Marked as answer by slickk_F Tuesday, February 26, 2013 11:27 PM
    Tuesday, February 12, 2013 11:49 PM
  • Composite PRIMARY KEY in this instance is not a good way to go.

    Take a look at designing with INT IDENTITY surrogate PRIMARY KEY. The assumption is that WardID by itself is not unique.

    CREATE TABLE [facilities].[warddetails]
    (  WardDetailsID INT IDENTITY(1,1) PRIMARY KEY,
      [WardID] [int] NOT NULL, 
      [WardName] [varchar](20) NOT NULL,
      UNIQUE (WardID, WardName));
     GO


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Wednesday, February 13, 2013 7:53 PM