August 7, 2015 at 10:22 am
H have 2 tables.
Table 1 is Interest
Table 2 is Note
Table 1 has a composite key build on it with fields (Vendor_ID & Offers)
Table 2 has an index on Vendor_ID that I need to have as a foreign key to the interest.vendor_ID field.
How can I accomplish this? SSMS is giving me grief because of the composite key.
See below
/****** Object: Table [dbo].[Interest] Script Date: 08/07/2015 12:13:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Interest](
[Vendor_ID] [int] NOT NULL,
[Bank] [int] NULL,
[Offers] [int] NOT NULL,
[Credit_Limit] [int] NULL,
[APR] [float] NULL,
[Delinquency_APR] [int] NULL,
[Down_Payment_Deposit_Amt] [int] NULL,
[Minimum_Pay_Percent] [int] NULL,
[Minimum_Pay_Amt] [int] NULL,
[Estimated_Payoff] [int] NULL,
[Possible_Payoff_Duration] [int] NULL,
[Late_Fee] [int] NULL,
[NSF_Fee] [int] NULL,
[Variable_Fix_Pay] [float] NULL,
[BankNumber] [int] NULL,
[InterestPercentage] [float] NULL,
[InterestDeferralMonths] [varchar](50) NULL,
[InterestDeferralType] [varchar](50) NULL,
[ProgramDescription] [varchar](50) NULL,
CONSTRAINT [PK_Interest] PRIMARY KEY NONCLUSTERED
(
[Vendor_ID] ASC,
[Offers] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
/****************************************************************************************/
/****** Object: Table [dbo].[Note] Script Date: 08/07/2015 12:14:58 ******/
CREATE TABLE [dbo].[Note](
[NoteID] [int] IDENTITY(1,1) NOT NULL,
[Vendor_ID] [int] NOT NULL,
[NOTES] [varchar](max) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
August 7, 2015 at 10:40 am
Add an identity column, and as it looks the table is a heap so use the opportunity and make it clustered.
August 7, 2015 at 10:48 am
But then how would I be able to reference all of the records in the Interest table that are a part of the composite?
Identity,Vendor, Offer
1001,380,1
1002,380,2
1003,380,3
1004,380,4
NoteID, VendorID, Description
1,380,Description
2,380,Description
3,380,Description
I need all the notes from vendor 380 to be seen in a single dataview.
wouldn't a identity separate all of the individual vendors into a different group?
How would I be able to get all of the vendor_id 380 records to be displayed in one set?
August 7, 2015 at 2:41 pm
You can't do this. Foreign Keys have to reference a unique record. That's what the KEY means in foreign key. Vendor_ID by itself is insufficient to uniquely identify a record in the Interest table. Vendor_ID should be a foreign key in both tables to a third table for Vendors.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 7, 2015 at 2:43 pm
That makes sense, thanks man.
I'll add another table to accommodate! I didn't think of that.
I really appreciate your post.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply