November 9, 2005 at 8:29 am
I'm using Enterprise Manager and trying to create some foreign key contraints on existing tables. Sometimes it works, but often I get an error like below:
'Orders' table saved successfully
'OrderStatus' table
- Unable to create relationship 'FK_OrderStatus_Orders'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_OrderStatus_Orders'. The conflict occurred in database 'signsysSQL', table 'Orders', column 'ORDER_ID'.
Orders is the parent table and OrderStatus the child, linked by ORDER_ID. I've also got the "Cascade Deletes" checkbox checked.
So what's wrong?
November 9, 2005 at 8:54 am
It looks like you have duplicate records. Are you checking existing data upon creation? I assume that Order_ID is a unique, primary key on Orders? How is OrderStatus keyed?
November 9, 2005 at 5:21 pm
Yes Orders.ORDER_ID is the primary key (identity column). OrderStatus has a STATUS_ID primary key which is also an identity column, plus an index on ORDER_ID + STATUS_DATE. I thought the conflict might be with the ORDER_ID in the index and tried deleting it, but I still ran into the problem.
These isn't the only tables I'm running into this problem on. Most of them are barking.
November 9, 2005 at 6:05 pm
The message returned on a foreign key constraint creation failure is, in my opinion, the most misleading message in SQL Server.
What it really means is that are some rows in the table that are failing the new constraint, and therefore, the constraint creation fails.
Try this SQL:
select *
from OrderStatus
where not exists
(select 1
from Orders
where Orders.OrderId = OrderStatus.OrderId
)
SQL = Scarcely Qualifies as a Language
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply