Incorrect syntax near the keyword 'KEY'.

rgb192
rgb192 used Ask the Experts™
on
CREATE TABLE uk_postcodes (
  postcode varchar(5) NOT NULL,
  x int(11) NOT NULL DEFAULT '0',
  y int(11) NOT NULL DEFAULT '0',
  latitude decimal(5,2) NOT NULL DEFAULT '0.00',
  longitude char(8) NOT NULL DEFAULT '0',
  town varchar(255) DEFAULT NULL,
  county varchar(255) DEFAULT NULL,
  PRIMARY KEY (postcode),
  KEY x (x,y)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='DanielGibbs.Net Free UK Postcode Database';



Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'KEY'.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
CREATE TABLE uk_postcodes (
  postcode varchar(5) NOT NULL,
  x int NOT NULL DEFAULT 0,
  y int NOT NULL DEFAULT 0,
  latitude decimal(5,2) NOT NULL DEFAULT 0,
  longitude char(8) NOT NULL DEFAULT 0,
  town varchar(255) DEFAULT NULL,
  county varchar(255) DEFAULT NULL,

  PRIMARY KEY (postcode)
)

What exactly is the key? Is it unique index? use: create unique index IDX_UK_PostCodes on uk_postcodes(X,Y)

I would also consider to change type of postcode column from varchar(5) to char(5) if all values are 5 digits

Author

Commented:
i dont know what the key is



-- Generation Time: May 14, 2010 at 06:58 PM
-- Server version: 5.1.41
-- PHP Version: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

-- Table structure for table `uk_postcodes`

CREATE TABLE IF NOT EXISTS `uk_postcodes` (
  `postcode` varchar(5) NOT NULL,
  `x` int(11) NOT NULL DEFAULT '0',
  `y` int(11) NOT NULL DEFAULT '0',
  `latitude` decimal(5,2) NOT NULL DEFAULT '0.00',
  `longitude` char(8) NOT NULL DEFAULT '0',
  `town` varchar(255) DEFAULT NULL,
  `county` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`postcode`),
  KEY `x` (`x`,`y`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='DanielGibbs.Net Free UK Postcode Database';

Commented:
This is for sure not T-SQL syntax. What are you trying to do? Port the system to mssql?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
i think this is mysql

Author

Commented:
want to create this database in sqlserver 2005

Commented:
If you want to create this table in SQL Server database, you can use the syntax from my first post.
Commented:
I would modify dwkor's suggestion slightly. Don't create a unique index, use a unique CONSTRAINT instead. Keys are logical features of the database and so it is more natural and conventional to use the constraint syntax (which incidentally creates a unique index as well). Constraints are more likely to be noticed and understood by developers and more likely to be recognised as keys by developer tools and management tools. Indexes less so.

CREATE TABLE uk_postcodes (
  postcode varchar(5) NOT NULL,
  x int NOT NULL DEFAULT '0',
  y int NOT NULL DEFAULT '0',
  latitude decimal(5,2) NOT NULL DEFAULT '0.00',
  longitude char(8) NOT NULL DEFAULT '0',
  town varchar(255) DEFAULT NULL,
  county varchar(255) DEFAULT NULL,
  PRIMARY KEY (postcode),
  CONSTRAINT x UNIQUE (x,y)
);

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial