[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 775
  • Last Modified:

Trying to figure out CONSTRAINT error in SQl 2008

I am workign through some tutorials and I am now stumepd as to how I can resolve my issue I am having.
Here is my Query:
USE SQL2008SBS
GO

--Must drop foreign key first, because it depends on the primary key
OrderDetail
    DROP CONSTRAINT fk_orderheadertoorderdetails
GO
 
--Change the existing primary keys to nonclustered
ALTER TABLE Orders.OrderHeader
    DROP CONSTRAINT pk_orderheader
GO
ALTER TABLE Orders.OrderHeader
    ADD CONSTRAINT pk_orderheader PRIMARY KEY NONCLUSTERED (OrderID)
GO
ALTER TABLE Orders.OrderDetail
    DROP CONSTRAINT pk_orderdetail
GO
ALTER TABLE Orders.OrderDetail
    ADD CONSTRAINT pk_orderdetail PRIMARY KEY NONCLUSTERED (OrderDetailID)
GO
--Recreate the foreign key (fix the misspelling from chapter 5)
ALTER TABLE Orders.OrderDetail
    ADD CONSTRAINT fk_orderheadertoorderdetails FOREIGN KEY (OrderID)
    REFERENCES Orders.OrderHeader (OrderID)    
GO
--Create new clustered indexes on the ShipDate/FinalShipDate columns
CREATE CLUSTERED INDEX icx_finalshipdate ON Orders.OrderHeader (FinalShipDate)
GO
CREATE CLUSTERED INDEX icx_shipdate ON Orders.OrderDetail (ShipDate)
GO

Here is my error:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'CONSTRAINT'.
Msg 3725, Level 16, State 0, Line 3
The constraint 'pk_orderheader' is being referenced by table 'OrderDetail', foreign key constraint 'fk_orderheadertoorderdetails'.
Msg 3727, Level 16, State 0, Line 3
Could not drop constraint. See previous errors.
Msg 1779, Level 16, State 0, Line 1
Table 'OrderHeader' already has a primary key defined on it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 2714, Level 16, State 4, Line 2
There is already an object named 'fk_orderheadertoorderdetails' in the database.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 1902, Level 16, State 3, Line 2
Cannot create more than one clustered index on table 'Orders.OrderHeader'. Drop the existing clustered index 'pk_orderheader' before creating another.
Msg 1913, Level 16, State 1, Line 1
The operation failed because an index or statistics with name 'icx_shipdate' already exists on table 'Orders.OrderDetail'.
0
manelson05
Asked:
manelson05
1 Solution
 
chapmandewCommented:
this:
--Must drop foreign key first, because it depends on the primary key
OrderDetail
    DROP CONSTRAINT fk_orderheadertoorderdetails

should be:

--Must drop foreign key first, because it depends on the primary key
ALTER TABLE OrderDetail
    DROP CONSTRAINT fk_orderheadertoorderdetails
0
 
manelson05Author Commented:
Rockin!

Sorry for the delay I have another I am going to psot soon.

thanks!

mark
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now