SqlDbx Forum

SqlDbx

You are not logged in.

#1 2018-06-12 10:48:57

ptoniolo
Member

Incorrect behavior when a column name contains a dot

If a column name has a dot character in it (".") the behavior of the SqlDbx user interface is inconsistent when that column is part of a constraint. Here is how to reproduce this behavior.

If you create a table like this:

create table test_dot1([a.b] int not null primary key)

, when you right-click un the UI and choose "Script CREATE", the constraint is written "primary key (a.b)", and this is cleary invalid: "a" is taken as a schema name.

If the name of the column has the sequence dot-space, like in:

create table test_dot2([a. b] int not null primary key)

, the behavior is even worse. The constraint, when asking for the sql code, is "primary key (a.[ b])", and probably for the same reason the column is not recognized in the user interface, i.e. the column is not marked with the "p" flag to indicate the pk.

I believe that your decision to quote the column names, that is now correct in the code you use to build the column names, needs to be extended in the code you use to build the name of the columns in the constraints. Apparently in that case you do not consider the dot as a legitimate part of the column name!

I am using the Professional version 3.11, 64 bit, no unicode.

Thanks
Pietroo

Offline

#2 2022-11-11 05:48:33

ptoniolo
Member

Re: Incorrect behavior when a column name contains a dot

After more than 4 years the problem is still there.

If you run this sql code:

create table aaatest
	(pk sysname primary key
	,[a.a] sysname foreign key references aaatest
	,[a a] sysname foreign key references aaatest
	,[a. a] sysname foreign key references aaatest
	,[a.a.] sysname foreign key references aaatest
	,[a a.] sysname foreign key references aaatest
	)

This is what you see in the UI:
aatest

And this is the code generated when you Script Create:

create table dbo.aaatest
	(
	  pk   sysname not null
	, [a.a] sysname not null
	, [a a] sysname not null
	, [a. a] sysname not null
	, [a.a.] sysname not null
	, [a a.] sysname not null
	, primary key (pk)
	, constraint FK__aaatest__a.a__29E1370A foreign key (a.a) references dbo.aaatest (pk)
	, constraint [FK__aaatest__a a__2AD55B43] foreign key ([a a]) references dbo.aaatest (pk)
	, constraint FK__aaatest__a.[ a__2BC97F7C] foreign key (a.[ a]) references dbo.aaatest (pk)
	, constraint FK__aaatest__a.a.__2CBDA3B5 foreign key (a.a.) references dbo.aaatest (pk)
	, constraint [FK__aaatest__a a].__2DB1C7EE foreign key ([a a].) references dbo.aaatest (pk)
	)
go

As you can see, the column names are correctly quoted, but not in the interpretation of the constraints!

Is it possibile to fix it?
I am using the most recent 6.17 pro 64 bit version.

BTW, let me be clear: I am not creating those strange column names, I have to use a database where these names are already defined with spaces and dots, and the behavior of SqlDbx is not coherent to what SQL Server itself is doing!

Last edited by ptoniolo (2022-11-11 05:52:14)

Offline

Board footer

Powered by FluxBB