How to tell SQL Server to use the default constraint name when adding one?

Hi guys,

I have this problem. I want to add a new primary key to a table but i want the name of the contstraint to be generated by the system. I have this TSQL code.

ALTER TABLE TableTest
ADD CONSTRAINT PRIMARY KEY (C1)


Reading the BOL, it says that if you don't supply a name for the constraint it generates one. But I get this error "Incorrect syntax near the keyword 'PRIMARY'".

If I add a name to the constraint, it works fine.
I'm using SQL Server 2000

Thanks
DarkneonAt least you have read and tried. Enclosed is a script that creates a table we use to trace deadlocks. The first created the primary key as it creates the table. The second alters the table after creation to add the primary key. Note that the constraint is named PK_tbl_trace_deadlock:



CREATE TABLE [dbo].[tblm_trace_deadlock] (
[row_id] [int] IDENTITY (1, 1) NOT NULL ,
[import_id] [int] NOT NULL ,
[BinaryData] [image] NULL ,
[DatabaseID] [int] NULL ,
[NTUserName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomainName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HostName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClientProcessID] [int] NULL ,
[ApplicationName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoginName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPID] [int] NULL ,
[Duration] [bigint] NULL ,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[EventSubClass] [int] NULL ,
[ObjectID] [int] NULL ,
[IndexID] [int] NULL ,
[IntegerData] [int] NULL ,
[ServerName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EventClass] [int] NULL ,
[Mode] [int] NULL ,
[DBUserName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tbl_trace_deadlock] PRIMARY KEY CLUSTERED
(
[row_id]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblm_trace_deadlock]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblm_trace_deadlock]
GO

CREATE TABLE [dbo].[tblm_trace_deadlock] (
[row_id] [int] IDENTITY (1, 1) NOT NULL ,
[import_id] [int] NOT NULL ,
[BinaryData] [image] NULL ,
[DatabaseID] [int] NULL ,
[NTUserName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomainName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HostName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClientProcessID] [int] NULL ,
[ApplicationName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoginName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPID] [int] NULL ,
[Duration] [bigint] NULL ,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[EventSubClass] [int] NULL ,
[ObjectID] [int] NULL ,
[IndexID] [int] NULL ,
[IntegerData] [int] NULL ,
[ServerName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EventClass] [int] NULL ,
[Mode] [int] NULL ,
[DBUserName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblm_trace_deadlock] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_trace_deadlock] PRIMARY KEY CLUSTERED
(
[row_id]
) ON [PRIMARY]
GO|||Hey tomh53,

Thanks for your input, but it's not quite what I am looking for. The script you submited creates a primary key with the name you supplied to it. I want to use the name that is automatically generated by the system. For example, in Entreprise Managed, when you set a primary key it generates a name for you. I want it to do the same thing, except the primary key is set programatically.

Here's what the BOL says:

"
constraint_name

Is the new constraint. Constraint names must follow the rules for identifiers, except that the name cannot begin with a number sign (#). If constraint_name is not supplied, a system-generated name is assigned to the constraint."

But like I said, it generates an error instead of a name for me.

Darkneon|||Why would you want to allow abiguity to exist when you have an opportunity to make something meaningful out of the constraint naming process. Allow me to quote from Inside SQL Server 200 by Kalen Delaney regarding system generated constraint names:


The advantage of explicitly naming your constraint rather than using the system-generated name is greater clarity. The constraint name is used in the error message for any constraint violation, so creating a name such as CUSTOMER_PK probably makes more sense to users than a name such as PK__customer__cust_i__0677FF3C. You should choose your own constraint names if such error messages are visible to your users.

The first two characters (PK) show the constraint typePK for PRIMARY KEY, UQ for UNIQUE, FK for FOREIGN KEY, and DF for DEFAULT. Next are two underscore characters, which are used as a separator. (You might be tempted to use one underscore to conserve characters and to avoid having to truncate as much. However, it's common to use a single underscore in a table name or a column name, both of which appear in the constraint name. Using two underscore characters distinguishes the kind of a name it is and where the separation occurs.)
Next comes the table name (customer), which is limited to 116 characters for a PRIMARY KEY constraint and slightly fewer characters for all other constraint names. For all constraints other than PRIMARY KEY, there are then two more underscore characters for separation followed by the next sequence of characters, which is the column name. The column name is truncated to five characters if necessary. If the column name has fewer than five characters in it, the length of the table name portion can be slightly longer.

And finally, the hexadecimal representation of the object ID for the constraint (68E79C55) comes after another separator. (This value is used as the id column of the sysobjects system table and the constid column of the sysconstraints system table.) Object names are limited to 128 characters in SQL Server 2000, so the total length of all portions of the constraint name must also be less than or equal to 128.|||But if you insist in shooting yourself in the foot, here it is:



CREATE TABLE [dbo].[bozo] (
row_id int IDENTITY (1, 1) NOT NULL ,
clownname varchar(20) NOT NULL ,
PRIMARY KEY CLUSTERED (row_id)
)
GO|||You should choose your own constraint names if such error messages are visible to your users.

This was the primary reason we did not mind using system generated names.
But it's ok now, I've talked with me collegue and we decided that we are going to give a name to the constraints.

By the way, the code snippet you provided is not good when you have a already existing table which is missing a primary key. Anyways, if someone knows the answers to my question, I am still interested. Otherwise, I close the discussion to my original question here.

Darkneon|||Look at the last two statements of the large block of code. The first one creates the table without a PK. The second one alters the table to add a PK. To take the small snippet, create the table, and then add the PK is:



CREATE TABLE [dbo].[bozo] (
row_id int IDENTITY (1, 1) NOT NULL ,
clownname varchar(20) NOT NULL
)
GO

alter table bozo
add
PRIMARY KEY CLUSTERED (row_id)
GO|||Thanks,

I tested it and it worked, with the name generated and everything. It is always good to know how to do stuff :D

Darkneon

How to tell if a function is called within a trigger

How can I tell whether a function has been called, along with the values that were passed into it? My SQL is generated by Visual Basic, and on an insert, a trigger fires, which then *might* call the function depending on a condition that's difficult to monitor. Ideally, I'd be looking for a simple function that would write to a log, or print a message somewhere. "Print" does not work within functions. I'm running SQL Server 2000.You could create a stored procedure that writes to a log, and then call that stored procedure from the trigger. Kinda messy, but will work.|||

1. Profiler

2. Put code in the trigger that inserts relevant values (funtion input parameters, record IDs) into a table at the point the function would be called. Comment out or remove this code after testing. I routinely do this in Try Catch blocks (or 2000 error handling) of sprocs during dev.

|||You also cannot perform an INSERT to a permanent table in a function. If this is important you might consider upgrading to SQL Server 2005 and using a SET CONTEXT_INFO to save some information. I guess you could call a procedure from the trigger but I am really not sure how much this will buy you -- you are sort-of already in a psedudo procedure since you are in a trigger.

How to summerize Calculated Member in SQL2000

I have created a virtual cube in SQL2000. Then, I have generated a
Calculated Member. When I did rollup into a higher level, SQL2000 did
re-calculate the member. However, I want to summerize the Calculated
member. The Solve Order could not be worked in this situation since a
measure, which is used to generate the Calculate Member, will be
available only in the lowerest level of the dimension. Please help me!Anote Sangkunakupt (anote_un@.hotmail.com) writes:
> I have created a virtual cube in SQL2000. Then, I have generated a
> Calculated Member. When I did rollup into a higher level, SQL2000 did
> re-calculate the member. However, I want to summerize the Calculated
> member. The Solve Order could not be worked in this situation since a
> measure, which is used to generate the Calculate Member, will be
> available only in the lowerest level of the dimension. Please help me!

You might get better response in a group like
microsoft.sqlserver.public.olap.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to stop PAD_INDEX being scripted

Our team checks SQL scripts into version control. On my machine, however, the generated script always differs slightly. When I generate a create table script, the primary key part always has PAD_INDEX = OFF. Nobody else's server generates this. Does anyone know what option or setting I need to change to prevent PAD_INDEX = OFF from being output?

WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

BOL specifies

When creating indexed views or manipulating rows in tables participating in an indexed view, seven SET options must be assigned specific values. The SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be ON. The SET option NUMERIC_ROUNDABORT must be OFF.

If any of these settings is different, data modification statements (INSERT, UPDATE, DELETE) on any table referenced by an indexed view fail and SQL Server raises an error listing all SET options that violate setting requirements. In addition, for a SELECT statement that involves an indexed view, if the values of any of the SET options are not the required values, SQL Server processes the SELECT without considering the indexed view substitution. This ensures correctness of query result in cases where it can be affected by the above SET options.

If the application uses a DB-Library connection, all seven SET options on the server must be assigned the required values. (By default, OLE DB and ODBC connections have set all of the required SET options correctly, except for ARITHABORT.)

|||

Thank you for taking the time to copy and paste. However, I don't see how your post is relevant.

I am asking what determines whether the PAD_INDEX option is included in scripted CREATE TABLE statements.

|||Is anyone able to help with this?