ALTER TABLE CONSTRAINT  
Author Message
alfredfarrugia





PostPosted: Fri Jan 30 17:28:06 CST 2004 Top

SQL Server Developer >> ALTER TABLE CONSTRAINT

I have column name called SendToQIP/ERS, and I'm trying to ADD DEFAULT to column like this

ALTER TABLE tblQuality /* sets default to 0 (zero) *
ADD CONSTRAINT Def_SendToQIP/ER
DEFAULT '0
FOR SendToQIP/ERS

This was an ACCESS Database file once before and naming conventions are not good. My error is because of the "/". Can anyone correct my script?

SQL Server92  
 
 
Louis





PostPosted: Fri Jan 30 17:28:06 CST 2004 Top

SQL Server Developer >> ALTER TABLE CONSTRAINT you cannot have / in the name.
create table tblQuality
(
SendToQIP/ERS varchar(1)
)
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '/'.

However:

create table tblQuality
(
[SendToQIP/ERS] varchar(1)
)

will work. Look at the rules for identifiers in books online.

Finally:

alter table tblQuality
/* sets default to 0 (zero) */
ADD CONSTRAINT [Def_SendToQIP/ERS]
DEFAULT '0'
FOR [SendToQIP/ERS]

A couple of opinions.

1. Don't prefix tables with tbl. It is clear that they are tables, and it
just bad practice. It won't hurt anything if you still want to though.

2. Don't uses "special" characters in table or column names. Just stick to
alphanumerics and underscores. It makes things much easier in cases like
this.

--
----------------------------------------------------------------------------
-----------

Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)



> I have column name called SendToQIP/ERS, and I'm trying to ADD DEFAULT to
column like this:
>
> ALTER TABLE tblQuality /* sets default to 0 (zero) */
> ADD CONSTRAINT Def_SendToQIP/ERS
> DEFAULT '0'
> FOR SendToQIP/ERS;
>
> This was an ACCESS Database file once before and naming conventions are
not good. My error is because of the "/". Can anyone correct my script?