Hi

I have data model as below;

I have created a unique constraint as below;

CREATE UNIQUE NONCLUSTERED INDEX [UNQ_StaffIDEventID] ON [dbo].[tblStaffBookings]
(
	[StaffID] ASC,
	[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

I now tried to create a link between two tables tblStaffBookings and tblEventStaffDisciplinary by dragging from tblEventStaffDisciplinary.StaffID to tblStaffBookings.StaffID and it gives me error as below;

What do I need to do to link tblEventStaffDisciplinary.StaffID to tblStaffBookings.StaffID?

Thanks

Regards

• Edited by Y a h y a Saturday, August 15, 2015 9:41 PM

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

http://stackoverflow.com/questions/25306380/the-columns-in-table-do-not-match-an-existing-primary-key-or-unique-constraint

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

http://stackoverflow.com/questions/25306380/the-columns-in-table-do-not-match-an-existing-primary-key-or-unique-cons

I have created a unique constraint as below;

CREATE UNIQUE NONCLUSTERED INDEX [UNQ_StaffIDEventID] ON [dbo].[tblStaffBookings]
(
	[StaffID] ASC,
	[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Your code creates an INDEX instead of creating a UNIQUE constraint. Please use the follow code: 

-- create UNIQUE CONSTRAINT
ALTER TABLE [dbo].[tblStaffBookings] 
ADD  CONSTRAINT [UNQ_StaffIDEventID] UNIQUE NONCLUSTERED 
( EventID, StaffID ) ;
GO

-- create FOREIGN KEY
ALTER TABLE [dbo].[tblEventStaffDisciplinary]  WITH CHECK 
ADD  CONSTRAINT [FK_tblEventStaffDisciplinary_tblStaffBookings] 
FOREIGN KEY([EventID], [StaffID])
REFERENCES [dbo].[tblStaffBookings] ([EventID], [StaffID])
GO

When using the Database Designer, also you can create a UNIQUE constraint, like the next picture:

Hi Saeid,

this is not an issue :-)
You can create FOREIGN KEY that REFERENCES "UNIQUE NONCLUSTERED INDEX" as well as "UNIQUE CONSTRAINT"

check the example in this blog:

http://ariely.info/Blog/tabid/83/EntryId/165/Unique-Constraints-vs-Unique-Indexes.aspx

There is issue is with the SSMS :-)

Hi Saeid

I only need relation on StaffID between the two tables as per my original post. I tried this;

ALTER TABLE [dbo].[tblEventStaffDisciplinary]  WITH CHECK 
ADD  CONSTRAINT [FK_tblEventStaffDisciplinary_tblStaffBookings] 
FOREIGN KEY([StaffID])
REFERENCES [dbo].[tblStaffBookings] ([StaffID])
GO

and got this error;

There are no primary or candidate keys in the referenced table 'dbo.tblStaffBookings' that match the referencing column list in the foreign key 'FK_tblEventStaffDisciplinary_tblStaffBookings'.

Thanks

Regards

Y a h y a, you can check the link I posted above for example. as Naomi said, you should use code instead of using the SSMS GUI (There is issue with the SSMS and not the SQL Server, as I mentioned in

In order to understand your specific issue, we need to see the tables structure. Let's g back to the start (this is something that you should have post from the start, and we should have asked you fro the start) :-)

Please post DDL (Data Definition Language) for the relevant tables

* By DDL, I mean queries to create the relevant tables including any index,constraint or other element that is part of the current tables structure. with this information we will have the option to check your case and give you a solution.

** You can use the SSMS GUI to get the DDL queries: Right-click the table in Object Explorer, and choose Script Table As -> Create To -> New Query Editor Window.

*** The most common issues is that people create index on several columns and then tries to create FOREIGN KEY by using single column.