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 |
|
shiyam198
Yak Posting Veteran
94 Posts |
 Posted - 2012-04-27 : 14:07:08
|
| Hi,I am running a set of upgrade scripts, it does a lot of things but main parts are:1.It drops the containts in this table, which is a compisite Primary key created on 2 colummsnALTER TABLE [dbo].[ts_order_charge_audit] DROP CONSTRAINT [PK_ts_order_charge_audit]2.It takes the data out, change the schema, modify data and put it all back in.3. It build the contraints again: ALTER TABLE [dbo].[ts_order_charge_audit] ADD CONSTRAINT [PK_ts_order_charge_audit] PRIMARY KEY CLUSTERED ( [ordercharge_id] ASC, [ordercharge_version] ASC )Now, this errors out saying "Cannot create constraints.So, first thing I thought was, there are duplicate records on the combinatins of columns [ordercharge_id] and [ordercharge_version].So, I checked with the following script:SELECT a.*FROM [ts_order_charge_audit] As [a] INNER JOIN ( SELECT [ordercharge_id] , [ordercharge_version] FROM [ts_order_charge_audit] GROUP BY [ordercharge_id] , [ordercharge_version] HAVING Count(*) >1 ) As [b] ON a.[ordercharge_id] = b.[ordercharge_id] AND a.[ordercharge_version] = b.[ordercharge_version]It doesn't show any results. Which means there are no duplicates.Why can I NOT create the constaints again? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
shiyam198
Yak Posting Veteran
94 Posts |
Posted - 2012-04-27 : 14:58:02
|
| Hi Tara,I ran the following and no records. Really puzzling. select ordercharge_id, ordercharge_version, COUNT(*)from ts_order_charge_auditgroup by ordercharge_id, ordercharge_versionHAVING (COUNT(*) > 1)If it is not the duplicate records, I am not sure what it is.. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Cannot create containts