May 27, 2010 at 8:21 am
Wasn't quite sure where to post this question as I'm looking for opinions on style, hope this is a good choice of forum.
What do you consider the more readable CREATE TABLE statement?
CREATE TABLE tested.NamedKey
(NamedKeyID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_NamedKey_NamedKeyID PRIMARY KEY CLUSTERED (NamedKeyID),
OtherKeyID int CONSTRAINT FK_OtherKey_NamedKey FOREIGN KEY REFERENCES OtherKey(OtherKeyID),
SomeColumn varchar(10) NULL
)
or
CREATE TABLE tested.NamedKey
(NamedKeyID int NOT NULL IDENTITY(1,1),
OtherKeyID int,
SomeColumn varchar(10) NULL
CONSTRAINT PK_NamedKey_NamedKeyID PRIMARY KEY CLUSTERED (NamedKeyID)
CONSTRAINT FK_OtherKey_NamedKey FOREIGN KEY REFERENCES OtherKey(OtherKeyID))
I'm leaning towards the latter as it makes it less likely that single lines will run off the screen, but I'm so used to declaring constraints on the same line that I think it'd take me awhile to get used to checking the bottom of the CREATE TABLE statement to find them. This question is really only arising because I want to start naming all my keys myself instead of letting SQL Server do it for me. I'm sick of having keys like NamedKey__NamedKeyID__F6G1FU894HH38284GH1.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 27, 2010 at 10:37 pm
This is largely a question of personal preference and formatting.
Specifying table constraints may make the definition easier to maintain in some circumstances; whereas column constraints may seem 'closer' to the data for some people, and might be more compact:
CREATE TABLE tested.NamedKey
(
NamedKeyID INTEGER NOT NULL
IDENTITY(1,1),
OtherKeyID INTEGER NOT NULL,
SomeColumn VARCHAR(10) NOT NULL
DEFAULT 'ABCDE',
IntColumn INTEGER NOT NULL
CONSTRAINT [CK tested.NamedKey IntColumn 0-100]
CHECK (IntColumn BETWEEN 0 AND 100),
CONSTRAINT [PK tested.NamedKey NamedKeyID]
PRIMARY KEY CLUSTERED (NamedKeyID),
CONSTRAINT [FK tested.NamedKey tested.OtherKey OtherKeyID]
FOREIGN KEY (OtherKeyID)
REFERENCES tested.OtherKey(OtherKeyID),
);
CREATE TABLE tested.NamedKey
(
NamedKeyID INTEGER NOT NULL
IDENTITY(1,1)
CONSTRAINT [PK tested.NamedKey NamedKeyID]
PRIMARY KEY CLUSTERED (NamedKeyID),
OtherKeyID INTEGER NOT NULL
CONSTRAINT [FK tested.NamedKey tested.OtherKey OtherKeyID]
REFERENCES tested.OtherKey(OtherKeyID),
SomeColumn VARCHAR(10) NOT NULL
DEFAULT 'ABCDE',
IntColumn INTEGER NOT NULL
CONSTRAINT [CK tested.NamedKey IntColumn 0-100]
CHECK (IntColumn BETWEEN 0 AND 100),
);
I don't have unbreakable rules on any of this, but I do tend to lean toward the second example - mostly because I like to think about CONSTRAINTs as I write each column definition, rather than waiting until the end.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 28, 2010 at 2:41 am
I'd write it like this: -
CREATE TABLE tested.namedkey
(
namedkeyid INT NOT NULL IDENTITY(1, 1)
CONSTRAINT pk_namedkey_namedkeyid PRIMARY KEY CLUSTERED (namedkeyid),
otherkeyid INT CONSTRAINT fk_otherkey_namedkey FOREIGN KEY REFERENCES
otherkey(
otherkeyid),
somecolumn VARCHAR(10) NULL
)
What bugs me the most is lack of capitals.
May 28, 2010 at 2:54 am
This was removed by the editor as SPAM
May 28, 2010 at 2:57 am
Our DBA used to claim he could tell what language you program with the most by the way you layout your SQL :hehe:
May 28, 2010 at 6:51 am
Paul White NZ (5/27/2010)
This is largely a question of personal preference and formatting.Specifying table constraints may make the definition easier to maintain in some circumstances; whereas column constraints may seem 'closer' to the data for some people, and might be more compact:
I don't have unbreakable rules on any of this, but I do tend to lean toward the second example - mostly because I like to think about CONSTRAINTs as I write each column definition, rather than waiting until the end.
Paul
Yep, I was just wondering what other people's personal preferences were to see if there were anything I was missing or if anyone had a really good reason for choosing one over the other. I tend to lean toward the "same line" formatting as well as that's how I'm used to declaring keys when not explicitly naming them, but I much prefer scrolling up and down to left and right so I've started declaring the constraints at the end.
Perhaps the answer is my company needs to get me a larger monitor!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 28, 2010 at 6:53 am
skcadavre (5/28/2010)
I'd write it like this: -
CREATE TABLE tested.namedkey(
namedkeyid INT NOT NULL IDENTITY(1, 1)
CONSTRAINT pk_namedkey_namedkeyid PRIMARY KEY CLUSTERED (namedkeyid),
otherkeyid INT CONSTRAINT fk_otherkey_namedkey FOREIGN KEY REFERENCES
otherkey(
otherkeyid),
somecolumn VARCHAR(10) NULL
)
What bugs me the most is lack of capitals.
But... you didn't use any capitals other than key words. Do you mean just in key words?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 28, 2010 at 6:55 am
stewartc-708166 (5/28/2010)
AS Paul stated, it is mainly a question of personal preference. There are no hard & fast rules.My preference is to put the constraints in-line with the column definition, so it is all together. (otherwise I might forget)
Yeah, not being used to looking at the bottom of the table definition for constraints, I'm hoping I don't forget they're there. Getting used to a new syntax is always a pain. Then again, you have to almost every time you learn something new. Even SSIS has different syntax than T-SQL.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 28, 2010 at 6:55 am
skcadavre (5/28/2010)
Our DBA used to claim he could tell what language you program with the most by the way you layout your SQL :hehe:
Heh, I'd love to see what he thinks my primary coding language is.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 28, 2010 at 7:25 am
Stefan Krzywicki (5/28/2010)
skcadavre (5/28/2010)
Our DBA used to claim he could tell what language you program with the most by the way you layout your SQL :hehe:Heh, I'd love to see what he thinks my primary coding language is.
Same here.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply