primary key name

F

flynn

Well-known member
Joined
Jul 28, 2005
Messages
58
When I use this syntax to create a new table (and primary key), the command works as it is supposed to:
Code: 
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:
Code: 
PK__promotion__78173351

but if I omit the "PRIMARY KEY" clause from the CREATE statement and run this command:
Code: 
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
 
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
 
sdlangers said:
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
Click to expand...


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)?
Code: 
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 said:
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
Click to expand...

Thats basically what I did. Thanks again sdlangers for the help (and the link).
 
Back
Top