December 12, 2011 at 1:26 pm
Hi,
Could you please give me an advice on how to implement this FK relationship between the tables?
CREATE TABLE Issuers
(
IssuerId BIGINT NOT NULL,
IssuerName VARCHAR(150),
IssuerRecordChangeCodeDateTime DATETIME NOT NULL,
DateCreated DATETIME NOT NULL CONSTRAINT [DF_Issuers_DateCreated] DEFAULT (getdate()),
...lots of other columns...,
CONSTRAINT [PK_Issuers] PRIMARY KEY CLUSTERED
(
[IssuerId],
[IssuerRecordChangeCodeDateTime]
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE Issues
(
IssuerId BIGINT,
IssuerName VARCHAR(150),
IssueRecordChangeCodeDateTime DATETIME NOT NULL,
IssueIdBIGINTNOT NULL,
DateCreated DATETIME NOT NULL CONSTRAINT [DF_Issues_DateCreated] DEFAULT (getdate()),
...lots of other columns...,
CONSTRAINT [PK_Issues] PRIMARY KEY CLUSTERED
(
[IssueId],
[IssueRecordChangeCodeDateTime]
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE BusinessIssuers
(
BusinessId [int] NOT NULL,
IssuerId [bigint] NOT NULL,
...lots of other columns...,
DateCreated DATETIME NOT NULL CONSTRAINT [DF_BusinessIssuers_DateCreated] DEFAULT (getdate()),
CONSTRAINT [PK_BusinessIssuers] PRIMARY KEY CLUSTERED
(
[BusinessId],
[IssuerId]
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I would like to create these foreign keys:
ALTER TABLE [BusinessIssuers] WITH CHECK ADD CONSTRAINT [FK_BusinessIssuers_Issuers] FOREIGN KEY ([IssuerId])
REFERENCES [Issuers] ([IssuerId])
GO
ALTER TABLE [dbo].[BusinessIssuers] CHECK CONSTRAINT [FK_BusinessIssuers_Issuers]
GO
ALTER TABLE [Issues] WITH CHECK ADD CONSTRAINT [FK_Issues_Issuers] FOREIGN KEY([IssuerId])
REFERENCES [Issuers] ([IssuerId])
GO
ALTER TABLE [Issues] CHECK CONSTRAINT [FK_Issues_Issuers]
GO
But I'm getting this error (the second error is the same, just the other table):
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.Issuers' that match the referencing column list in the foreign key 'FK_BusinessIssuers_Issuers'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_BusinessIssuers_Issuers' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
I understand that the error is because I reference only one column from the primary key. I need to have both of these columns -- [IssuerId],[IssuerRecordChangeCodeDateTime]!
I read that the other way to do this is to use an IDENTITY column with the primary key on the table plus a UNIQUE CONSTRAINT on the columns that have to be unique. Then I should be able to reference one column in the FK but it doesn't work for me.
CREATE TABLE Issuers
(
Id_Number int IDENTITY(1,1),
IssuerId BIGINT NOT NULL,
IssuerName VARCHAR(150),
IssuerRecordChangeCodeDateTime DATETIME NOT NULL,
DateCreated DATETIME NOT NULL CONSTRAINT [DF_Issuers_DateCreated] DEFAULT (getdate()),
...lots of other columns...,
CONSTRAINT [PK_Issuers] PRIMARY KEY CLUSTERED
(
[Id_Number]
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT UNQ_Issuers_IssuerId_IssuerRecordChangeCodeDateTime UNIQUE ([IssuerId],[IssuerRecordChangeCodeDateTime]),
) ON [PRIMARY]
Sorry if it's a stupid question but I'm fairly new to SQL and just learning!
Thanks,
Denis
December 12, 2011 at 1:55 pm
The problem is your original primary key. You have a composite key in your Issuers table of IssuerId, IssuerRecordChangeCodeDateTime. Then you try to make only IssuerID a foreign key. That won't work because a foreign key has to point to the primary key from the base table and you are trying build your foreign key on only half of the actual primary key. I would suggest you take the IssuerRecordChangeCodeDateTime column out of your primary key. Not exactly sure what that is. That reads like a really long winded way of saying something ModDate??? Assuming that is what that field is, it should NOT be part of your key because it is intended to change. Primary keys should be a static and permanent value.
You have two options here:
1) remove the IssuerRecordChangeCodeDateTime field from the primary key (This is probably the best approach).
2) add IssuerRecordChangeCodeDateTime to your other tables and add that to the foreign key.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2011 at 6:00 am
Thanks for your reply! Everything (including the column names) comes from the external feed. I need to have both of them as the primary because there will be instances of, say:
IssuerId IssuerRecordChangeCodeDateTime
1 2011-12-13 10:00:00
1 2011-12-13 11:00:00
1 2011-12-13 11:30:00
And I'll need to store all of them in the table but I'll show only one MAX(IssuerRecordChangeCodeDateTime).
Denis
December 13, 2011 at 7:17 am
Then you will have to add the date field as part of your foreign key. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2011 at 7:20 am
Thank you, Sean! I appreciate your help.
Denis
December 13, 2011 at 7:39 am
DenisT (12/13/2011)
Thanks for your reply! Everything (including the column names) comes from the external feed. I need to have both of them as the primary because there will be instances of, say:IssuerId IssuerRecordChangeCodeDateTime
1 2011-12-13 10:00:00
1 2011-12-13 11:00:00
1 2011-12-13 11:30:00
And I'll need to store all of them in the table but I'll show only one MAX(IssuerRecordChangeCodeDateTime).
Denis
I don't agree with you here. Data feed might be coming from external sources but not the Database Schema (DB structure). That you are hosting / designing, right? By adding Datatime field in PK you anyways voided the purpose of PK (well almost).
Please go with Sean’s first suggestion. Accept the datetime column value from feed but don’t validate it with PK constraint. Add a single column in your PK definition use the same as FK in child tables.
December 13, 2011 at 7:53 am
Dev (12/13/2011)
DenisT (12/13/2011)
Thanks for your reply! Everything (including the column names) comes from the external feed. I need to have both of them as the primary because there will be instances of, say:IssuerId IssuerRecordChangeCodeDateTime
1 2011-12-13 10:00:00
1 2011-12-13 11:00:00
1 2011-12-13 11:30:00
And I'll need to store all of them in the table but I'll show only one MAX(IssuerRecordChangeCodeDateTime).
Denis
I don't agree with you here. Data feed might be coming from external sources but not the Database Schema (DB structure). That you are hosting / designing, right? By adding Datatime field in PK you anyways voided the purpose of PK (well almost).
Please go with Sean’s first suggestion. Accept the datetime column value from feed but don’t validate it with PK constraint. Add a single column in your PK definition use the same as FK in child tables.
Another option that would accomplish both sides is to add an identity as your primary key and a unique constraint on the other two fields.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2011 at 7:55 am
OK! If I follow your suggestion (which I'd really like to do), how will I store the combinations of the IssuerId and DataTime columns? As far as I know, I won't be able to do that. I really don't like this idea either but I just cannot figure out any other way. I'd like to join these table on IssuerId in the queries.
This primary key will be too big and impractical, included into every non-clustered index. I just don't know how to get around it!
Would it make sense to implement a combination of the surrogate key amd unique non-clustered index (IssuerId,DateTime) for the Issuers table? Then I can link the other two via this key and join the tables using the index. Does it make sense?
Denis
December 13, 2011 at 7:58 am
Thank you, guys! I think I see my options now. I guess it's time to play now.
Denis
December 13, 2011 at 8:05 am
Sean Lange (12/13/2011)
...Another option that would accomplish both sides is to add an identity as your primary key and a unique constraint on the other two fields.
Another Column, Another Key, Clustered / Non Clustered? :w00t:
December 13, 2011 at 8:22 am
Dev (12/13/2011)
Sean Lange (12/13/2011)
...Another option that would accomplish both sides is to add an identity as your primary key and a unique constraint on the other two fields.
Another Column, Another Key, Clustered / Non Clustered? :w00t:
Like this although I would probably change some column names to make them more clear. 😛
CREATE TABLE [dbo].[Issues](
[IssuesID] [bigint] IDENTITY(1,1) NOT NULL,
[IssuerId] [bigint] NULL,
[IssuerName] [varchar](150) NULL,
[IssueRecordChangeCodeDateTime] [datetime] NOT NULL,
[IssueId] [bigint] NOT NULL,
[DateCreated] [datetime] NOT NULL,
CONSTRAINT [PK_Issues] PRIMARY KEY CLUSTERED
(
[IssuesID] 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 [dbo].[Issues] ADD CONSTRAINT [DF_Issues_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
GO
create unique index IX_Issues_IssuerID_IssueRecordChangeCodeDateTime on Issues(IssuerID,IssueRecordChangeCodeDateTime)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2011 at 8:31 am
Agree on it.
What I am thinking on following requirement, IssuerId + IssuerRecordChangeCodeDateTime is business key (assumption). Queries will be filed on business key. With the added column Optimizer has to Bookmark Lookup to reach to child tables (if join any). Am I guessing right?
December 13, 2011 at 8:57 am
You're right! I'll need talk to developers to see what queries they're planning on running and then look at the execution plans.
Denis
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply