Hello, I have a database with a table that had it's primary key as 3 columns,
I finally added a new column to be an idenity column but kept the original
primary key to keep things working. But when I create new tables I want to
link on my new field. By doing something like this.

ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD CONSTRAINT
[fk_Table2_TableID_Table1] FOREIGN KEY([TableID])
REFERENCES [dbo].[Table1] ([TableID])
GO

But I receive this error message when doing it
There are no primary or candidate keys in the referenced table 'dbo.Table1'
that match the referencing column list in the foreign key
'fk_Table2_TableID_Table1'.

How do I make the new idenity column in my originally table a candidate key?
If you have a example in script that would be great.

Thanks

> How do I make the new idenity column in my originally table a candidate
> key?
> If you have a example in script that would be great.
>

One method is to add a UNIQUE constraint:

ALTER TABLE dbo.Table1
ADD CONSTRAINT UQ_Table1_TableID
UNIQUE(TableID);

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Don" <Don@discussions.microsoft.com> wrote in message
news4FD5AF0-6264-4B41-A8A6-F7DCC07D3F48@microsoft.com...
> Hello, I have a database with a table that had it's primary key as 3
> columns,
> I finally added a new column to be an idenity column but kept the original
> primary key to keep things working. But when I create new tables I want
> to
> link on my new field. By doing something like this.
>
> ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD CONSTRAINT
> [fk_Table2_TableID_Table1] FOREIGN KEY([TableID])
> REFERENCES [dbo].[Table1] ([TableID])
> GO
>
> But I receive this error message when doing it
> There are no primary or candidate keys in the referenced table
> 'dbo.Table1'
> that match the referencing column list in the foreign key
> 'fk_Table2_TableID_Table1'.
>
> How do I make the new idenity column in my originally table a candidate
> key?
> If you have a example in script that would be great.
>
> Thanks
>