Wrong key column displayed

When primary key defined in CREATE TABLE instruction.

Example:

You've got a syntax error, 2 actually, there is a comma missing before PRIMARY KEY and constraint name.

PRIMARY KEY keyword can appear either immediately after the key column or as a separate syntax element. In the last case constraint name is required.

There is a confusion caused by CREATE TABLE definition not being strict enough (in my opinion). The one SysOp marked as a syntax error is a grave result of it. It's syntactically correct but very confusing and does not do what you would think it should. There are many ways to define the primary key, therefore it can be quite complicated to retrieve it correctly. Those below are all valid (and it does not cover all possibilities) and since SA does not show them correctly it might not cover all of them:

	Code:
	CREATE TABLE #ltSomeTable (

0.

	Code:
	id INT NOT NULL PRIMARY KEY ,sub_id INT NOT NULL ,comment NVARCHAR(20) NULL

or 1.

	Code:
	id INT NOT NULL PRIMARY KEY (id, sub_id) ,sub_id INT NOT NULL ,comment NVARCHAR(20) NULL

or 3.

	Code:
	id INT NOT NULL ,sub_id INT NOT NULL PRIMARY KEY (id, sub_id) ,comment NVARCHAR(20) NULL

or 4.

	Code:
	id INT NOT NULL ,sub_id INT NOT NULL ,comment NVARCHAR(20) NULL ,CONSTRAINT pk_ltsometable PRIMARY KEY (id, sub_id)

or 5.

	Code:
	id INT NOT NULL ,sub_id INT NOT NULL ,comment NVARCHAR(20) NULL ,PRIMARY KEY(id, sub_id)

or 6. (see the lack of comma)

	Code:
	id INT NOT NULL ,sub_id INT NOT NULL ,comment NVARCHAR(20) NULL PRIMARY KEY(id, sub_id)

	Code:
	)

#6 (without the comma) is primarily used to mark the single column it is place after as a primary key (see #0).

All of these (except #0.) seem to yield the same result (that is, id and sub_id being the primary key) and SA is not very consistent what to mark as primary key except for the missing comma case where it definitely show the wrong colum. But even refreshing the cache does stir things sometimes removing or adding the key icon to arbitrary columns.

Referring to the temp table with its full name

	Code:
	SELECT * FROM tempdb.dbo.#ltSomeTable

will show the correct primary key, though it has to be created first for that to work.

EDIT: all the above is for SQL Server 2k8, it might not apply to other DBMSes.

Alas, my knowledge is insufficient there. I only use #0 and #4 + ALTER TABLE ADD CONTSTRAINT clause, that's for sure. I know no reason for the presence of the others.

While #0 is identified correctly by SA, #4 is not (or so it seems).

I use #5 quite a bit for temp tables. SA picks it up as a column.

Case #5 will be supported in version 6.2. Please wait for a few more days.