The Double Table

Question

Post reply

The Double Table

Steve Jones - SSC Editor

SSC Guru

Points: 736478
More actions
August 2, 2021 at 12:00 am

#3901377

Comments posted to this topic are about the item The Double Table

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 1

This was a good question. Thanks!

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

You are welcome. It was one of those that just struck me. I know I've seen the error, but seeing that this is a real object was something I wanted to point out.

Aubrey Love SSC Journeyman Points: 95 More actions · Answer 3

This is a good reminder to all that a constraint is an object just like tables, views, functions, triggers, etc. Since you are trying to create two objects with the same name, it obviously will fail.

Good one.

Aubrey W Love
aka: PailWriter
https://www.aubreywlove.com/

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

When I looked the problem page, no error was included on the page.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

BTW... this is also a really good reason to have some naming standards. We follow the standard that PKs will always use the form of PK_tablenamehere.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Aubrey Love SSC Journeyman Points: 95 More actions · Answer 6

Maybe I’m out of sync with all others, but I have always preferred the 3-part naming convention.

“PK_tableName_columnName”

This allows for distinct constraints in one table. For example, if I have multiple DEFAULT or CHECK constraints on one table. (Which I often do.)

Of course, using either camel case or pascal case for readability sake.

Just my 2 cents worth.

Aubrey W Love
aka: PailWriter
https://www.aubreywlove.com/

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 7

I tend to want grouping of objects, so I'll do tablenamePK or tablenameAK for an alternate unique key.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

I don't think including the column name in the PK is a bad idea especially since I do tend to include column names in other indexes. The only reason why I don't is because of consistency... if you know the name of a table, you know the name of the PK. While it's not a common thing to do, I've also seen the definition of what the PK is change. Again, no big deal. I'm just lazy, I guess. 😀

I hadn't considered tablename_indexname before but, thinking about that and working with indexes a whole lot more in the last 5 years, especially the last 2, that actually sounds like a pretty handy naming convention. My non-clustered index names can sometimes be a little weird, though. I generally try to stick with IX_ and then the names of the key columns for the index name but there are also special purpose indexes that will begin with the abbreviation of RPT_ (for "Report") or CVR_ (for "Cover") followed by some other special purpose name (like the name of a report, for example), especially if the indexes have more than two columns for the index.

I guess my biggest reason for probably not liking the tablename_indexname format is because of what you can see or not see when looking at execution plans.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Aubrey Love SSC Journeyman Points: 95 More actions · Answer 9

Jeff,

I don’t see a need to call the “column name” on the PRIMARY KEY either. But, since I use the 3-part naming convention on all the other constraints, I just naturally use it on the PRIMARY KEY as well, for uniformity sake.

That's just one of my little quirks.

This reply was modified 3 years, 4 months ago by Aubrey Love.

Aubrey W Love
aka: PailWriter
https://www.aubreywlove.com/

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 10

Just curious, but what do you do when you have the same table in multiple schemas? For example - you could have the same table in a staging schema and a production schema?

Either way - as long as there is some consistency in the format and names, the next person should be able to figure out the original intent of that index/constraint. Not that the original intent is still valid 😉

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Aubrey Love SSC Journeyman Points: 95 More actions · Answer 11

Jeffrey,

Actually I've never had that issue. (Yet!)

In that scenario, I would have to break tradition and add the schema name prefix to the table name when naming the constraint.

Aubrey W Love
aka: PailWriter
https://www.aubreywlove.com/

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 12

For me, I'm woefully inconsistent. I'd have:

create table dbo.Sale
( SaleID int not null constraint SalePK primary Key
, SaleDate date
)
go
create table auditing.Sale
( SaleID int not null constraint AuditingSalePK primary Key
, SaleDate date
)
go

I tend to use the schema as a pre-pend. However, I know I'm not great here, as if the Auditing.Sale existed first, it would likely have SalePK, and then I'd be renaming or doing something poorly, like dboSalePK.

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 13

Steve Jones - SSC Editor wrote:

For me, I'm woefully inconsistent. I'd have:
create table dbo.Sale
( SaleID int not null constraint SalePK primary Key
, SaleDate date
)
go
create table auditing.Sale
( SaleID int not null constraint AuditingSalePK primary Key
, SaleDate date
)
go
I tend to use the schema as a pre-pend. However, I know I'm not great here, as if the Auditing.Sale existed first, it would likely have SalePK, and then I'd be renaming or doing something poorly, like dboSalePK.

I think most are inconsistent in this regard - in fact, I think most don't take the time to set a standard for these types of objects. At least I haven't seen any formalized recommendations on how constraints and indexes should be named - and we see a lot of these get system generated names.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Aubrey Love SSC Journeyman Points: 95 More actions · Answer 14

Jeffry,

I suppose my wife is somewhat correct in accusing me of being OCD. I have a tendency to group my constraints by constraint type, then table name, then column name.

All of my tables, views, etc. are created this way.

CREATE TABLE dbo.Sales2(
colID INT IDENTITY NOT NULL 
, productName VARCHAR(30) 
, purchaseDate DATE 
, purchaseTime TIME(0)
);
GO

ALTER TABLE dbo.Sales2
ADD CONSTRAINT PK_Sales2_ColID 
PRIMARY KEY(colID) ;
GO

ALTER TABLE dbo.Sales2
ADD CONSTRAINT DFT_Sales2_PurchaseDate
DEFAULT GETDATE() FOR purchaseDate;
GO

ALTER TABLE dbo.Sales2
ADD CONSTRAINT DFT_Sales2_PurchaseTime
DEFAULT GETDATE() FOR purchaseTime;
GO

I never call the constraints within the CREATE TABLE query; I don’t like leaving the naming convention to Microsoft chaos.

But that’s just me.

Different strokes....

Aubrey W Love
aka: PailWriter
https://www.aubreywlove.com/