Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
azeemigi
Starting Member
5 Posts |
 Posted - 2010-01-01 : 04:52:47
|
-------------------------------------------------- Table: tblOrders------------------------------------------------USE EROBGOIF OBJECT_ID(N'dbo.tblOrders',N'U')IS NULL --Checks whether tblOrders object available. If not create one CREATE TABLE dbo.tblOrders ( ORDER_ID int identity(1,1) NOT NULL, PRODUCT_ID int NOT NULL CONSTRAINT FK_Product_Order FOREIGN KEY REFERENCES tblProducts(PRODUCT_ID), ORDER_QUANTITY int NOT NULL, ORDER_TOTAL int NOT NULL, ORDER_DATE datetime NOT NULL, CONSTRAINT PK_tblOrders PRIMARY KEY(ORDER_ID,PRODUCT_ID) )GO-------------------------------------------------- Table: tblOrder_Cus------------------------------------------------USE EROBGOIF OBJECT_ID(N'dbo.tblOrder_Cus',N'U')IS NULL --Checks whether tblOrder_Cus object available. If not create one CREATE TABLE dbo.tblOrder_Cus ( ORDER_ID int identity(1,1) NOT NULL CONSTRAINT FK_Order_OrderCus FOREIGN KEY REFERENCES tblOrders(ORDER_ID), CUS_ID int NOT NULL CONSTRAINT FK_Customer_OrderCus FOREIGN KEY REFERENCES tblCustomers(CUS_ID), CONSTRAINT PK_tblOrder_Cus PRIMARY KEY(CUS_ID, ORDER_ID) )GO
When I execute the above SQL statements I get the following error. [Please note that this is a part of my DDL in the database EROB]. I would be grateful if you couls help me to solve below error.Msg 1776, Level 16, State 0, Line 2There are no primary or candidate keys in the referenced table 'tblOrders' that match the referencing column list in the foreign key 'FK_Order_OrderCus'.Thank you  |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-01-01 : 05:16:04
|
| the error is quite self explainatory.you are having a foreign key with no correspoding primary key with refrential constraint defined.PBUH |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-01 : 10:11:27
|
| Why do you have a composite primary key on the Orders table? Surely the OrderID column alone should be the pk?--Gail ShawSQL Server MVP |
|
|
|
azeemigi
Starting Member
5 Posts |
Posted - 2010-01-03 : 01:37:53
|
| Well I was thinking to keep track of all Products that customer purchases into a one Order_ID at a time. It's like one order can have many products at a time. That's why I'm having a composite primary key. Since this is the 1st time I'm working with SQL Server for my project I couldn't really figure out the problem here. Idera, how to define referential constraint programmatically? Thank you Idera and Gail Shaw |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-03 : 03:57:07
|
| But you have the order column specified as identity. That means that each line gets a new number. Also, that violates normalisation rules. Order_Date depends only on the order, not on the product id.If you want to do that, break it down into two tables, Order and OrderLineItems (or similar name). The Order table with the columns relating to just the order itself (and one row per order) and the OrderLineItems with a composite key of OrderID (from the order table) and ProductID and just the columns that relate to the items ordered--Gail ShawSQL Server MVP |
|
|
|
azeemigi
Starting Member
5 Posts |
Posted - 2010-01-04 : 13:47:40
|
Thanks for pointing out the violation of normalization rule. I modified my database design. Since tblOrders has composite primary key, I added the keyword "UNIQUE" next to ORDER_ID in tblOrders. Now it's working. tblOrders.ORDER_ID int UNIQUE NOT NULL,Thank you |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-05 : 02:02:43
|
| You're still violating normalisation, and this is not a particularly good fix. There's no point in a composite primary key when one of the columns in it is unique.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
How to tackle Error Msg: 1776?