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
