July 22, 2019 at 11:58 pm
Here is my code:
CREATE TABLE ProjectCodes
(
ProjectID varchar(22),
ProjectName varchar(25),
Level char(1),
[Project Classification] varchar(14),
[Project Type] varchar(11),
Billable char(1), DEFAULT 'Y',
[Allow Charging] char(1), DEFAULT 'Y',
Active char(1), DEFAULT 'Y',
[Contract No] char(17),
[Task Order No] char(17),
CONSTRAINT PK_ProjectCodes_ProjectID PRIMARY KEY CLUSTERED (ProjectID ASC)
)
The error message is:
Msg 142, Level 15, State 2, Line 3
Incorrect syntax for definition of the 'TABLE' constraint.
July 23, 2019 at 1:12 am
You need to put your defaults in parentheses, eg:
DEFAULT ('Y')
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 23, 2019 at 1:14 am
And if you're at all OCD with your database object names (or maybe you like to check everything in to source control & therefore avoid random names), consider using extended syntax, eg:
Billable CHAR(1)
CONSTRAINT DF_ProjectCodes_Billable
DEFAULT ('Y'),
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 23, 2019 at 2:36 am
I never would have guessed that.
This website indicates that parenthesis are not required:
https://www.w3schools.com/sql/sql_default.asp
And the SQL online documentation doesn't seem to indicate that parenthesis are required either (see the pic in the attached file).
July 23, 2019 at 2:40 am
I tried this and I'm still getting the same error. I also tried using double quotes around the default values instead of single quotes and that didn't work. Also if it helps, I'm using SSMS 17.9.1
CREATE TABLE ProjectCodes
(
ProjectID varchar(22),
ProjectName varchar(25),
Level char(1),
[Project Classification] varchar(14),
[Project Type] varchar(11),
Billable char(1), DEFAULT ('Y'),
[Allow Charging] char(1), DEFAULT ('Y'),
Active char(1), DEFAULT ('Y'),
[Contract No] char(17),
[Task Order No] char(17),
CONSTRAINT PK_ProjectCodes_ProjectID PRIMARY KEY CLUSTERED (ProjectID ASC)
)
July 23, 2019 at 4:27 am
July 23, 2019 at 6:03 am
Please find the query below:-
CREATE TABLE ProjectCodes
(
ProjectID varchar(22) not null,
ProjectName varchar(25) null,
Level char(1) null,
[Project Classification] varchar(14) null,
[Project Type] varchar(11) null,
Billable char(1) DEFAULT ('Y'),
[Allow Charging] char(1) DEFAULT ('Y'),
Active char(1) DEFAULT ('Y'),
[Contract No] char(17) null,
[Task Order No] char(17) null,
CONSTRAINT PK_ProjectCodes_ProjectID PRIMARY KEY CLUSTERED (ProjectID ASC)
)
July 23, 2019 at 1:21 pm
As a matter of safety in the future, you should always include the schema name in the object name.
Also, you should get into the habit of using properly placed semi-colons because not using them has been deprecated. If they every make them required, you won't be in such deep Kimchee trying to add missing semi-colons in all of your code.
I'd also suggest defining the NULLability of every column (I assumed they should all be NOT NULL on this table but you should also explicitly define NULL where needed) and, if the code has to be submitted to a DBA for deployment, making the code a lot easier to review by doing a little vertical alignment.
I also wish that MS would standardize casing on T-SQL keywords on auto-generated code.
Last but certainly not least, there is no way that I'd ever include spaces or other special characters in the columns so as to make them require brackets. You're not formatting a report here and you will end up hating the brackets.
I also happen to be a fairly recent fan of leading commas for too many reasons to post here but I didn't do that in the code below.
With all of that in mind, this is the way I would do it (not including the awful bracketed column names or a few other SQL "sins", IMHO).
CREATE TABLE dbo.ProjectCodes
(
ProjectID VARCHAR(22) NOT NULL,
ProjectName VARCHAR(25) NOT NULL,
Level CHAR(1) NOT NULL,
[Project Classification] VARCHAR(14) NOT NULL,
[Project Type] VARCHAR(11) NOT NULL,
Billable CHAR(1) NOT NULL DEFAULT ('Y'),
[Allow Charging] CHAR(1) NOT NULL DEFAULT ('Y'),
Active CHAR(1) NOT NULL DEFAULT ('Y'),
[Contract No] CHAR(17) NOT NULL,
[Task Order No] CHAR(17) NOT NULL,
CONSTRAINT PK_ProjectCodes_ProjectID PRIMARY KEY CLUSTERED (ProjectID ASC)
)
;
I'd also change "Billable" to "IsBillable" and "Active" to "IsActive" just for reader clarification and so people understand that the columns are Y/N flags. I'd also add constraints to the Y/N columns to ensure that only a "Y" or an "N" could be used.
You're also missing FK's for things like ProjectType and ProjectClassification.
If the ProjectID has a particular format, you might also want to add a constraint to enforce the correct format.
As for the Level column, if it's supposed to contain digits, I'd set the datatype to TINYINT. On that same note, I'd also change the datatype for "IsBillable" and "IsActive" to TINYINT and use a 1 for "Yes" and a 0 for "No" along with constraints to ensure that only 1 or 0 could ever be used in the column. I wouldn't use the BIT datatype here, either. The reason for the proposed change is it will make coding damned easy to determine how many projects are billable or active using a simple SUM aggregate. Y/N and BIT datatypes will require a conversion to aggregate them.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2019 at 2:06 pm
Following on from Jeff's excellent advice, I'd like to add a little more. I'd suggest removing all spaces from your column names ... makes your code much less cumbersome because you won't need those square brackets at all.
For user presentation, you can of course add the spaces back in (and how you do that depends on the tool you are using).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 23, 2019 at 2:40 pm
Following on from Jeff's excellent advice, I'd like to add a little more. I'd suggest removing all spaces from your column names ... makes your code much less cumbersome because you won't need those square brackets at all. For user presentation, you can of course add the spaces back in (and how you do that depends on the tool you are using).
Excellent... Great minds think alike. From my rather long winded post...
Last but certainly not least, there is no way that I'd ever include spaces or other special characters in the columns so as to make them require brackets. You're not formatting a report here and you will end up hating the brackets.
Having spaces or other special characters in column names is one of the top items in my personal "SQL Bozo-no-no" list and so I'm glad that you're emphasizing that particular fault.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2019 at 5:02 pm
Jeff, your advice was so excellent, I clearly didn't bother reading it properly 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 23, 2019 at 5:50 pm
Jeff, your advice was so excellent, I clearly didn't bother reading it properly 🙂
Just to be sure, it wasn't meant as any kind of slam for missing something, Phil. I absolutely thought it excellent emphasis on a problem that would eat the OP's face off in the very near future.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2019 at 6:24 pm
Just to be sure, it wasn't meant as any kind of slam for missing something, Phil. I absolutely thought it excellent emphasis on a problem that would eat the OP's face off in the very near future.
Jeff, I took it in the way you intended, no issue!
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 23, 2019 at 6:33 pm
Jeff Moden wrote:Just to be sure, it wasn't meant as any kind of slam for missing something, Phil. I absolutely thought it excellent emphasis on a problem that would eat the OP's face off in the very near future.
Jeff, I took it in the way you intended, no issue!
BWAAA-HAAAA!!! Thanks, Phil. Tough week for me. I've been "shot at and missed, s**t at and hit" and so I was just making sure.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2019 at 1:10 am
It was the commas I had before the keyword DEFAULT. I was in the mindset of separating things with commas which is why I did that. Thank you.
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply