Last post Oct 27, 2016 06:57 AM by wmec
Oct 25, 2016 02:05 PM|abhijithmanipal|LINK
Hi All, I am facing some issues while adding the unique key constraint as foreign key -
Adding the Unique Constraint -
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE type_desc = 'UNIQUE_CONSTRAINT' AND OBJECT_NAME(parent_object_id) = 'Countries' AND OBJECT_NAME(OBJECT_ID) = 'UC_Countries')
ALTER TABLE [dbo].Countries
ADD CONSTRAINT [UC_Countries] UNIQUE NONCLUSTERED
Adding the Foreign Key relationship -
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_States_Countries]') AND parent_object_id = OBJECT_ID(N'[dbo].[States]'))
ALTER TABLE [dbo].[States] WITH CHECK ADD CONSTRAINT [FK_States_Countries] FOREIGN KEY([CountryCode])
REFERENCES [dbo].[Countries] ([UC_Countries])
Let me know what is the issue in the above query.
Thanks in advance !!
Oct 26, 2016 11:26 AM|PatriceSc|LINK
When posting some code, please always tell what happens (exact error message or the best English translation you can or the unexpected behavior you see).
Here the first thing I see is that you are using a constraint name rather than a column name in your REFERENCES statement (ie what happens if you try REFERENCES [dbo].[Countries] ([CountryCode]) instead ?
You should see an error message about that (something complaining that the UC_Countries column doesn't exists)...
Oct 27, 2016 06:57 AM|wmec|LINK
Do like this
CREATE TABLE Orders
O_Id int NOT NULL,
OrderNo int NOT NULL,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)