Getting warning while creating constraint !

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Getting warning while creating constraint !

Author

Topic

DilliGrg
Starting Member

7 Posts

Posted - 2007-03-01 : 13:54:46

I was trying to create a unique constraint on these fields for this table, I am getting this warning. Will this give problems in the insert/update for this table? Any suggestions?


ALTER TABLE wr_admin.WR_REPORT
ADD CONSTRAINT U_WR_REPORT UNIQUE NONCLUSTERED (wr_report_name, wr_report_location, wr_report_provider_id)

Warning! The maximum key length is 900 bytes. The index 'U_WR_REPORT' has maximum length of 1054 bytes. For some combination of large values, the insert/update operation will fail.


Thanks,

Name
---------   
Dilli Grg     

(1 row(s) affected)

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-01 : 14:02:38

Well it seems pretty straight forward. Does it all have to be unique? Can reduce the length on the columns?

What does
SELECT MAX(LEN(wr_report_name))
, MAX(LEN(wr_report_location))
, MAX(LEN(wr_report_provider_id))
FROM wr_admin.WR_REPORT

Give you?

What are the sizes of these columns?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

DilliGrg
Starting Member

7 Posts

Posted - 2007-03-01 : 14:09:03

Thanks Brett for the quick response. Even though the data in these columns do not have max length but the column itself was defined as wr_report_location varchar(1000). I will have to see if it can be reduced. But as long as the future insert/update are not longer than 900 limit, it should be ok, i guess. What you think?

quote:
Originally posted by X002548

Well it seems pretty straight forward. Does it all have to be unique? Can reduce the length on the columns?

What does
SELECT MAX(LEN(wr_report_name))
, MAX(LEN(wr_report_location))
, MAX(LEN(wr_report_provider_id))
FROM wr_admin.WR_REPORT

Give you?

What are the sizes of these columns?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam


Thanks,

Name
---------   
Dilli Grg     

(1 row(s) affected)

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-01 : 17:17:52

I guess...I mean you can test it, but would you want to take the chance that it will fail?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

DilliGrg
Starting Member

7 Posts

Posted - 2007-03-01 : 18:21:57

I agree. I have made necessary adjustments for this column.

quote:
Originally posted by X002548

I guess...I mean you can test it, but would you want to take the chance that it will fail?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam


Thanks,

Name
---------   
Dilli Grg     

(1 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources