Alignment for list of columns of a constraint in create table statement

torsten.strauss

Hi Redgate Team,

the alignment of the column list within a constraint is wrong:

CREATE TABLE test
(
    col______________1 int NOT NULL
,   col______________2 int NOT NULL
,   col______________3 int NOT NULL
,   col______________4 int NOT NULL
,   col______________5 int NOT NULL
,   col______________6 int NOT NULL CONSTRAINT UQ UNIQUE
                                        (
                                            col______________1
                                        , col______________2
                                        , col______________3
                                        , col______________4
                                        , col______________5
                                        , col______________6
                                        )
);

rayherring

Your constraint is actually a column constraint. If you want it to be a table constraint you need to precede it with a comma. See the BNF in BOL for Create Table.
I know that it seems a unique constraint could only be a Table level constraint but the syntax clearly allows it to be defined as either a table or column constraint.
And just to add to confusion both forms create a table level constraint object .
I imagine that SQL Prompt just treats your format is as part of the column definintion. Try it by adding a perceding comma and see how the format changes.
HTH

Michael Clark

Hi @torsten.strauss,

Thanks for reporting. Is this another issue after updating or was it there before?

Would it be possible to create a code block for your expected after format?

Best wishes,
Michael

torsten.strauss

Hi Michael,

thanks for your reply.

Currently it looks like this after formatting:

CREATE TABLE test
(
	col______________1 int NOT NULL
,	col______________2 int NOT NULL
,	col______________3 int NOT NULL
,	col______________4 int NOT NULL
,	col______________5 int NOT NULL
,	col______________6 int NOT NULL CONSTRAINT UQ UNIQUE
										(
											col______________1
										, col______________2
										, col______________3
										, col______________4
										, col______________5
										, col______________6
										)
);

So you can see that col______________1 in the UNIQUE constraint column list is not aligned with the other column names below.
Apart from this it makes no sense to have CONSTRAINT UQ UNIQUE on the same line as col6 cause the unique constraint is independent from this.
Also wondering why the content of the constraint starts after that many tabs ...
So the formatted code should look like this:

CREATE TABLE test
(
	col______________1 int NOT NULL
,	col______________2 int NOT NULL
,	col______________3 int NOT NULL
,	col______________4 int NOT NULL
,	col______________5 int NOT NULL
,	col______________6 int NOT NULL 
-- new line and aligned 
	CONSTRAINT UQ UNIQUE
	(
		col______________1
-- aligned
	, 	col______________2
	, 	col______________3
	, 	col______________4
	,	col______________5
	, 	col______________6
										)
	);

Thanks!

torsten.strauss

Hi rayherring !

You are right!
SQL Server will create the unique table and column constraint in exactly the same way internally.
But to get the desired format I have to add a comma.
Thanks for clarification!

Torsten