Re: Newbie creating table question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 08/09/04 

Date: Mon, 9 Aug 2004 09:45:53 -0400

(a) drop the tbl prefix, it is superfluous and just makes it harder to type
and find.

(b) check the CREATE TABLE syntax in Books Online. Constraints need a name,
and you must tell it what kind of constraint it is. e.g.

[CurStatus] [varchar] CONSTRAINT
    checkCurStatus
    CHECK
    (
        CurStatus IN
        (
            'On Course',
            'Not Attending',
            'Got Job',
            'Completed Course'
        )
    ),

(c) what kind of value is "Got Job"? Do you really want to store these
strings for every ? I would prefer a lookup table where this representation
is a TINYINT. Less redundancy, and much easier to change "Got Job" to
something more English-like. ;-) 

-- 
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul in Harrow" <PaulinHarrow@discussions.microsoft.com> wrote in message
news:E7987DC1-D56D-4915-BD82-E1B68F735F8B@microsoft.com...
> Back again
> I'm trying to create a table the correct way (not using the Enterprise
> Manager) and I'm having problems. Here's my script:
> CREATE TABLE [dbo].[tblAdditionalInfo] (
> [LDUserName] [varchar] (35) NOT NULL Primary Key ,
> [FirstName] [varchar] (15) NOT NULL ,
> [MiddleName] [varchar] (15) NOT NULL ,
> [NINumber] [varchar] (10) NULL ,
> [Jobcentre] [varchar] (15) NULL ,
> [CurStatus] [varchar] CONSTRAINT( 'On Course' , 'Not Attending' , 'Got
Job'
> , 'Completed Course' ) ,
> [DatedChange] [smalldatetime] NULL default getdate(),
> [AsylumSeekerYN] [varchar] (3) NULL CONSTRAINT ('Yes' , 'No' ) ,
> [StudentLocation] [varchar] (15) Null CONSTRAINT ('College' , 'Home
Learner'
> ) ,
> [SpecialNeedsYN] [varchar] (3) NULL CONSTRAINT ('Yes' , 'No' ) ,
> [IsActive] [varchar] (10) NULL CONSTRAINT ('Active' , 'Inactive' ) ,
> [LogBookNo] [varchar] (13) Null ,
> [DateIssued] [smalldatetime] NULL ,
> [Scheme] [varchar] (25) NULL)
> GO
>
> I'm getting "Server: Msg 170, Level 15, State 1, Line 7
> Line 7: Incorrect syntax near '('."
>
> Help please!
> (Not all the fields with Constraints will be entered at any one time thats
> why they are NULL)
> Thanks
>
> Paul
> (proberly try to run before he can walk)
>

Relevant Pages

  • Re: subroutine stack and C machine model
    ... You stated elsewhere that constant expressions are a ... section so presumably you include in "syntax" the constraints whilst ... Rather, I believe that, if you follow the syntax down far ... support such a rigid distinction. ...
    (comp.lang.c)
  • Re: Implicit Declaration
    ... constraint violation or a syntax violation ... The standard certainly treats them differently, putting syntax rules ... places constraints in sections named "Constraints". ...
    (comp.lang.c)
  • Re: subroutine stack and C machine model
    ... You stated elsewhere that constant expressions are a ... section so presumably you include in "syntax" the constraints whilst ... Rather, I believe that, if you follow the syntax down far ... was your canonical non-syntactic distinction. ...
    (comp.lang.c)
  • Re: int pointing to char
    ... I honestly believe that applying semantics to code that has ... >> constraint violations is enough to determine if the code is erroneous ... >the meaning is "code with this syntax, subject to these constraints, ... If the syntax does not match then the ...
    (comp.lang.c)
  • Re: How do you print out details of the file structure of a database?
    ... "One new feature added to the Jet CREATE TABLE syntax is Check ... Constraints. ... This new SQL grammar allows the user to specify business ...
    (microsoft.public.access.gettingstarted)