primary key name

[flynn]

When I use this syntax to create a new table (and primary key), the command works as it is supposed to:

CREATE TABLE "DBO"."promotion"
(
"item_id"       		integer NOT NULL ,
"start_date"    		datetime NOT NULL ,
"end_date"      		datetime NOT NULL ,
"buy_quantity"  		integer NULL ,
"receive_quantity"      	integer NULL ,
"receive_item_id"       	integer NULL ,
"promo_id"      		varchar(17) ,
"description"   		text NULL ,
"customer_key"  		integer NULL ,
"parent_item_id"        	integer NULL , 
 PRIMARY KEY ("item_id"),
)	

 

The only problem is that the Primary Key gets named with a seemingly random set of characters at the end of the name, as in:

PK__promotion__78173351

 

but if I omit the "PRIMARY KEY" clause from the CREATE statement and run this command:

ALTER TABLE "promotion" ADD CONSTRAINT "PK_promotion" PRIMARY KEY ("item_id")

 

the primary key is named as I wanted it ("PK_promotion").

 

Can I get the "PRIMARY KEY" clause to name the key from a string that I specify? Or am I going to have to CREATE the table first, then issue an ALTER command?

 

tia,

flynn

[sdlangers]

if this is sql server, then you can use this syntax:

 

CREATE TABLE employee

(

emp_id empid

... other columns...

CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED

)

 

that will create the employee table, with a primary key called 'PK_emp_id'

[flynn]

if this is sql server, then you can use this syntax:

 

CREATE TABLE employee

(

emp_id empid

... other columns...

CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED

)

 

that will create the employee table, with a primary key called 'PK_emp_id'

 

 

Sorry, yes it is MS SQL 2005 and thank you, that seems to have done it for me.

 

Would this be the correct way to add a PK on multiple columns (it seems to work ok)?

CREATE TABLE "DBO"."pricing"
(
"item_id"       		integer NOT NULL ,
"price_class"   		smallint NOT NULL 
	CONSTRAINT "PK_pricing" PRIMARY KEY ("item_id", "price_class") ,
"quantity"      		integer NOT NULL ,
"price" 			numeric(10,2) NOT NULL ,
"profit_code"   		char(1) NULL , 
)

[sdlangers]

yes - that should work - check out this link

 

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0777.asp

 

 

also you can simply check these things with trial and error - run the code and then try to insert 2 identical records - or check the table in enterprise manager

[flynn]

also you can simply check these things with trial and error - run the code and then try to insert 2 identical records - or check the table in enterprise manager

 

That's basically what I did. Thanks again sdlangers for the help (and the link).