December 12, 2012 at 12:59 pm
I am creating a table with data types.
CREATE TABLE [dbo].[ED] (
[EDId] int IDENTITY(1,1) NOT NULL,
[Amount] int NOT NULL,
[ESId] varchar(50) NOT NULL
)
CREATE TABLE [dbo].[ES] (
[ESId] varchar(50) NOT NULL,
[AC] nvarchar(max) NOT NULL
)
Where there is a one-one mapping between dbo.ED to dbo.ES. The data in the ESId column looks like 001CC4936EB. So can anyone suggest which would be the best data type for this column. And as it is one to one mapping so ESId would become Primary key in dbo.ES. And I made it unique in dbo.ED and my query looks like below.
-- Creating Unique constraint on [ESId] in table 'ES'
ALTER TABLE [dbo].[ES]
ADD CONSTRAINT [IX_ESID] UNIQUE ( [ESID] )
GO
-- Creating foreign key on [EStId] in table 'ES'
ALTER TABLE [dbo].[ES] WITH NOCHECK ADD CONSTRAINT [FK_ES_ED] FOREIGN KEY([ESId])
REFERENCES [dbo].[ED] ([ESId])
NOT FOR REPLICATION
But still it throws an error: There are no primary or candidate keys in the referenced table 'dbo.ED' that match the referencing column list in the foreign key 'FK_ES_ED'.
Thanks,
December 12, 2012 at 1:17 pm
You do not have a primary key on the dbo.ED table. A foreign key can only be created to reference a primary/unique key in another table. As for datatype, varchar seems like a fair choice, unless there is more to the data in that column than what you have explained.
December 12, 2012 at 1:33 pm
Forgot to mention EDId is the primary key in dbo.ED table
December 12, 2012 at 1:34 pm
The ED table needs at least 1 unique field, and you can create the UNIQUE constraint right in the table definition if you don't care what name it has. Here's the code I tested, and it works fine until I remove the word UNIQUE from the ESId field, at which point it gives the error you describe.
CREATE TABLE dbo.ED (
[EDId] int IDENTITY(1,1) NOT NULL,
[Amount] int NOT NULL,
[ESId] varchar(50) NOT NULL UNIQUE
)
CREATE TABLE dbo.ES (
[ESId] varchar(50) NOT NULL,
[AC] nvarchar(max) NOT NULL
)
ALTER TABLE dbo.ES
ADD CONSTRAINT [IX_ESID] UNIQUE ( [ESId] )
GO
ALTER TABLE dbo.ES WITH NOCHECK ADD CONSTRAINT [FK_ES_ED] FOREIGN KEY([ESId])
REFERENCES dbo.ED ([ESId])
NOT FOR REPLICATION
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 12, 2012 at 1:58 pm
tanvishriya (12/12/2012)
-- Creating Unique constraint on [ESId] in table 'ES'ALTER TABLE [dbo].[ES]
ADD CONSTRAINT [IX_ESID] UNIQUE ( [ESID] )
GO
-- Creating foreign key on [EStId] in table 'ES'
ALTER TABLE [dbo].[ES] WITH NOCHECK ADD CONSTRAINT [FK_ES_ED] FOREIGN KEY([ESId])
REFERENCES [dbo].[ED] ([ESId])
NOT FOR REPLICATION
to setup a foreign key from ES to ED in this way, you'd have to add a UNIQUE constraint on the ESID column of the ED table. Was this foreign key supposed to go from ED to ES?
December 12, 2012 at 2:17 pm
Chris Harshman (12/12/2012)
tanvishriya (12/12/2012)
-- Creating Unique constraint on [ESId] in table 'ES'ALTER TABLE [dbo].[ES]
ADD CONSTRAINT [IX_ESID] UNIQUE ( [ESID] )
GO
-- Creating foreign key on [EStId] in table 'ES'
ALTER TABLE [dbo].[ES] WITH NOCHECK ADD CONSTRAINT [FK_ES_ED] FOREIGN KEY([ESId])
REFERENCES [dbo].[ED] ([ESId])
NOT FOR REPLICATION
to setup a foreign key from ES to ED in this way, you'd have to add a UNIQUE constraint on the ESID column of the ED table. Was this foreign key supposed to go from ED to ES?
Come to think of it, Chris, that's a distinct possibility. And it might be why the OP was trying to add a unique constraint, but maybe just added it to the wrong table ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 12, 2012 at 3:16 pm
Thanks,
Its working perfect. But i could see that if we add unique to the column in the table itself, then only the script is working fine.. Otherwise it throws error. I am curious what is the making it is making, as :
script works fine: when unique is mentioned on column at table and unique constraint is added in alter statement and then foreign key is added.
Script throws error: when unique is not mentioned on column at table , but unique constraint is added in alter statement and then foreign key is added.
And yes there is a one-to one mapping from dbo.ED to dbo.ES implies one ED transaction can have only one ES.
Thanks,
December 12, 2012 at 3:32 pm
I suppose the best way to decribe it is that a foreign key relationship requires a primary key or unique constraint on the referenced column in the referenced table, so even though you specified a column in your REFERENCES clause:
REFERENCES [dbo].[ED] ([ESId])
SQL Server doesn't know how to enforce that relationship without the primary key or unique constraint.
http://msdn.microsoft.com/en-us/library/ms177463(v=SQL.105).aspx
December 12, 2012 at 4:00 pm
ok got it thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply