[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 942
  • Last Modified:

Create Table thru SQL with Auto_Increment

I'm having difficulty with my create table code.. any ideas on whats wrong?

<cfquery name="Addtable" datasource="#application.db#">
  create table tbl_UserLinks
  (
       link_ID_PK INT NOT NULL default 0 auto_increment,
     address char(150),
     sent_by char(20),
     primary key (link_ID_PK)
  )
</cfquery>
0
M_Corkish
Asked:
M_Corkish
  • 5
  • 4
  • 2
  • +2
1 Solution
 
YogCommented:
I guess

<cfquery name="Addtable" datasource="#application.db#">
 create table tbl_UserLinks
 (
      link_ID_PK INT NOT NULL default 0 auto_increment PRIMARY KEY,
    address char(150),
    sent_by char(20)
 )
</cfquery>
0
 
techy6Commented:
What DB are you using?  I think there's a problem with your last line - the primary key statement.  If I was doing this on Oracle, I'd do it like this:

create table tbl_UserLinks
 (
    link_ID_PK number(8) NOT NULL primary key,
    address varchar2(150),
    sent_by varchar2(20)
 )

and use a sequence in order to auto-increment your key values.

0
 
M_CorkishAuthor Commented:
I'm using ms-sql, but also testing it locally sometimes on a different db (local access db)

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
reitzenCommented:
For SQL Server, use the following:

CREATE TABLE
  tbl_UserLinks
  (
     link_ID INT IDENTITY (1,1) NOT NULL,
  ,  address char(150) NOT NULL
  ,  sent_by char(20) NOT NULL
  )
  ON PRIMARY
0
 
jimmy282Commented:
here is correct SQL for ms-sql

CREATE TABLE [dbo].[SUBSCRIBERS] (
     [id] [int] IDENTITY (1, 1) NOT NULL ,
     [Name] [varchar] (50) NULL ,
     [Email] [varchar] (50) NULL
) ON [PRIMARY]


ALTER TABLE [dbo].[SUBSCRIBERS] WITH NOCHECK ADD
     CONSTRAINT [PK_SUBSCRIBERS] PRIMARY KEY  CLUSTERED
     (
          [id]
     )  ON [PRIMARY]



Change your table names and column names.

Jimmy
0
 
M_CorkishAuthor Commented:
neither ideas seemed to work.  I don't know whats wrong..
0
 
reitzenCommented:
I got this to work on my SQL Server 7.0.  Run it using Query Analyzer:

CREATE TABLE [dbo].[tbl_UserLinks] (
     [link_ID] [int] IDENTITY (1, 1) NOT NULL ,
     [address] [char] (150) NOT NULL ,
     [sent_by] [char] (20) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_UserLinks] WITH NOCHECK ADD
     CONSTRAINT [PK_tbl_UserLinks] PRIMARY KEY  CLUSTERED
     (
          [link_ID]
     )  ON [PRIMARY]
GO
0
 
jimmy282Commented:
what error?
0
 
M_CorkishAuthor Commented:
this is the error i get on the local ms access DB:
=============

37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.

0
 
jimmy282Commented:
this is for Access


create table tbl_UserLinks
 (
      link_ID_PK    AUTOINCREMENT NOT NULL ,
    address char(150),
    sent_by char(20),
PRIMARY KEY (link_ID_PK)
 )



Jimmy
0
 
M_CorkishAuthor Commented:
Jimmy, that did the trick!  so what exactly is going on there.. is it just the auto_increment becomes autoincrement that was throwing it?

cheers everyone.

0
 
jimmy282Commented:
yes

auto_increment is used in MYSQL.
in MS-Sql its called Identity(1,1)
In Msaccess its autoincrement.

HTH
Jimmy
0
 
M_CorkishAuthor Commented:
so if my web server uses ms-sql, but the database i put on their server is an access db (not a commerical site, so access is fine! 8) should i be using the ms-sql code, or the access code?

0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now