April 30, 2015 at 9:51 am
All,
Environment: Microsoft SQL Server Standard Edition (64-bit), 10.0.5520.0
I was doing a code review for another developer and came across this code:
CREATE TABLE dbo.#ABC
(
ReportRunTime DATETIME
,SourceID VARCHAR(3)
,VisitID VARCHAR(30)
,BaseID VARCHAR(25)
,OccurrenceSeqID INT
,[DateTime] DATETIME
,SeqID VARCHAR(20)
,QueryID VARCHAR(40)
,Response VARCHAR(241)
,CONSTRAINT ABC_PK PRIMARY KEY CLUSTERED
( ReportRunTime, VisitID, SourceID, BaseID, OccurrenceSeqID, [DateTime], SeqID, QueryID, Response )
);
This EXECUTES with no error or warning message.
However, if I change this to CREATE the PK in an ALTER TABLE statement, I get the (expected by me) error:
CREATE TABLE dbo.#ABC
(
ReportRunTime DATETIME
,SourceID VARCHAR(3)
,VisitID VARCHAR(30)
,BaseID VARCHAR(25)
,OccurrenceSeqID INT
,[DateTime] DATETIME
,SeqID VARCHAR(20)
,QueryID VARCHAR(40)
,Response VARCHAR(241)
);
ALTER TABLE dbo.#ABC
ADD CONSTRAINT ABC_PK PRIMARY KEY CLUSTERED
( ReportRunTime, VisitID, SourceID, BaseID, OccurrenceSeqID, [DateTime], SeqID, QueryID, Response );
==> Msg 8111, Level 16, State 1, Line 17 Cannot define PRIMARY KEY constraint on nullable column in table '#ABC'.
==> Msg 1750, Level 16, State 0, Line 17 Could not create constraint. See previous errors.
(Please note: As the #ABC table is an actual copy of a few of the columns in a "permanent" table, I will likely change the definition as follows such that the columns are defined to match the names / datatypes / NULLability:
SELECT TOP 0
CAST('01-01-1980' AS DATETIME) AS [ReportRunTime]
,SourceID
,VisitID
,BaseID
,OccurrenceSeqID
,[DateTime]
,SeqID
,QueryID
,Response
INTO
dbo.#ABC
FROM
dbo.ABC;
).
Any thoughts, references I missed, etc. are much appreciated.
Thanks,
~ Jeff
April 30, 2015 at 10:10 am
If you specify PF within table definition, it automatically marks the columns as not null.
If you don't, then you have to specify not null for each column first so that the PK will be able to be created.
This works
CREATE TABLE dbo.test
(
ReportRunTime DATETIME not null
,SourceID VARCHAR(3) not null
,VisitID VARCHAR(30) not null
,BaseID VARCHAR(25) not null
,OccurrenceSeqID INT not null
,[DateTime] DATETIME not null
,SeqID VARCHAR(20) not null
,QueryID VARCHAR(40) not null
,Response VARCHAR(241) not null
);
ALTER TABLE dbo.test
ADD CONSTRAINT ABC_PK PRIMARY KEY CLUSTERED
( ReportRunTime, VisitID, SourceID, BaseID, OccurrenceSeqID, [DateTime], SeqID, QueryID, Response );
April 30, 2015 at 10:19 am
Thanks for the quick response and (formerly unknown to me) information. I rarely use default syntax as I work with a variety of technical and non-technical personnel, and it seems more clearly defined and readable for many of them not to do so.
~ Jeff
April 30, 2015 at 12:19 pm
Don't name the constraint, as constraint names have to be unique.
ALTER TABLE dbo.test
ADD PRIMARY KEY CLUSTERED
( ReportRunTime, VisitID, SourceID, BaseID, OccurrenceSeqID, [DateTime], SeqID, QueryID, Response );
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 30, 2015 at 12:26 pm
ScottPletcher (4/30/2015)
Don't name the constraint, as constraint names have to be unique.ALTER TABLE dbo.test
ADD PRIMARY KEY CLUSTERED
( ReportRunTime, VisitID, SourceID, BaseID, OccurrenceSeqID, [DateTime], SeqID, QueryID, Response );
Since you can name constraints, under what circumstances would you do so?
April 30, 2015 at 12:40 pm
Personally, I name everything I can name ... makes queries of the system tables / views / functions easier to filter. Might also be unresolved control issues, though.
"Control your own destiny or someone else will." ~ Jack Welch
April 30, 2015 at 12:48 pm
Lynn Pettis (4/30/2015)
ScottPletcher (4/30/2015)
Don't name the constraint, as constraint names have to be unique.ALTER TABLE dbo.test
ADD PRIMARY KEY CLUSTERED
( ReportRunTime, VisitID, SourceID, BaseID, OccurrenceSeqID, [DateTime], SeqID, QueryID, Response );
Since you can name constraints, under what circumstances would you do so?
If you want a name that's actually reasonable to type 🙂
April 30, 2015 at 12:53 pm
I typically follow this naming convention:
TableName_PK
TableName_FK01 etc.
TableName_CK01 etc.
similar with indexing:
TableName_CLIX
TableName_NCIX01 etc.
As far as typing, with copy / paste, or tools like Red Gate, not so much typing...
I could sleep at night WITHOUT naming my constraints, but I sleep BETTER by naming them.
~ Jeff
April 30, 2015 at 12:56 pm
ScottPletcher (4/30/2015)
Don't name the constraint, as constraint names have to be unique.ALTER TABLE dbo.test
ADD PRIMARY KEY CLUSTERED
( ReportRunTime, VisitID, SourceID, BaseID, OccurrenceSeqID, [DateTime], SeqID, QueryID, Response );
The original version was in tempdb, which was what I was referring to EDIT: what I meant to refer to.
You can't name constraints in tempdb because the second user that attempts to run the code gets a duplicate name error.
For permanent tables, I name all constraints (including DEFAULT, although that capability is apparently going to go away at some point). I use tablename__xx[_colname], where xx is the type of constraint/index: CL, PK, IX, DF, CK, etc..
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 30, 2015 at 1:01 pm
ScottPletcher (4/30/2015)
ScottPletcher (4/30/2015)
Don't name the constraint, as constraint names have to be unique.ALTER TABLE dbo.test
ADD PRIMARY KEY CLUSTERED
( ReportRunTime, VisitID, SourceID, BaseID, OccurrenceSeqID, [DateTime], SeqID, QueryID, Response );
The original version was in tempdb, which was what I was referring to EDIT: what I meant to refer to.
You can't name constraints in tempdb because the second user that attempts to run the code gets a duplicate name error.
For permanent tables, I name all constraints (including DEFAULT, although that capability is apparently going to go away at some point). I use tablename__xx[_colname], where xx is the type of constraint/index: CL, PK, IX, DF, CK, etc..
Okay, now, what is the benefit of explicitly naming constraints over allowing the system to name them. I was actually asked this question but could not come up with a real answer that actually satisfied me. Plus, I haven't had the time since I was asked this question to do much research on this topic.
April 30, 2015 at 1:20 pm
Lynn Pettis (4/30/2015)
ScottPletcher (4/30/2015)
ScottPletcher (4/30/2015)
Don't name the constraint, as constraint names have to be unique.ALTER TABLE dbo.test
ADD PRIMARY KEY CLUSTERED
( ReportRunTime, VisitID, SourceID, BaseID, OccurrenceSeqID, [DateTime], SeqID, QueryID, Response );
The original version was in tempdb, which was what I was referring to EDIT: what I meant to refer to.
You can't name constraints in tempdb because the second user that attempts to run the code gets a duplicate name error.
For permanent tables, I name all constraints (including DEFAULT, although that capability is apparently going to go away at some point). I use tablename__xx[_colname], where xx is the type of constraint/index: CL, PK, IX, DF, CK, etc..
Okay, now, what is the benefit of explicitly naming constraints over allowing the system to name them. I was actually asked this question but could not come up with a real answer that actually satisfied me. Plus, I haven't had the time since I was asked this question to do much research on this topic.
As I see it, with explicit names:
1) I always know the underlying table
2) all indexes for the same table list together sorting by just index name (without having to sort by table name first), with the clustered index listed before other indexes (perhaps by lucky coincidence?)
3) I can, optionally, add the full lead key column name(s) to the index name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 30, 2015 at 2:41 pm
One benefit is that I can tell the constraints and/or indexes I create to support my customers vs. those they may have created. I have a highly varied customer base for our database reporting product...some use it intensely and create lots of customized reports, etc. Some run only what we provide. I can quickly tell which constraints (more often indexes) they have created for their own use.
April 30, 2015 at 2:43 pm
Lynn Pettis (4/30/2015)
ScottPletcher (4/30/2015)
ScottPletcher (4/30/2015)
Don't name the constraint, as constraint names have to be unique.ALTER TABLE dbo.test
ADD PRIMARY KEY CLUSTERED
( ReportRunTime, VisitID, SourceID, BaseID, OccurrenceSeqID, [DateTime], SeqID, QueryID, Response );
The original version was in tempdb, which was what I was referring to EDIT: what I meant to refer to.
You can't name constraints in tempdb because the second user that attempts to run the code gets a duplicate name error.
For permanent tables, I name all constraints (including DEFAULT, although that capability is apparently going to go away at some point). I use tablename__xx[_colname], where xx is the type of constraint/index: CL, PK, IX, DF, CK, etc..
Okay, now, what is the benefit of explicitly naming constraints over allowing the system to name them. I was actually asked this question but could not come up with a real answer that actually satisfied me. Plus, I haven't had the time since I was asked this question to do much research on this topic.
It's a matter of consistency across multiple instances. If you have a database that can be created with a script as part of the installation of a product, having system named constraints means that the constraint names are different in different installations.
If you have to send out a maintenance script that, for instance, drops and recreates a primary key you have to use the constraint name to drop it. If you don't know the contraint name because it's system generated, you have to read it from the metadata, hence complicating the script. If the constraint had the same name in all the installations, you could have used that name safely instead.
-- Gianluca Sartori
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply