CODEWITHSUNDEEP
mysql
Saint
Saint

Reputation: 480

ERROR importing sql database

I am a beginner with the sql databases. I am trying to import a database to phpmyadmin and I am getting an error saying:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT_AUTOINCREMENT, firstName varchar (30) NOT_NULL, lastName var' at line 2

my sql code looks like that:

use myAddressBook
;

create table names (
    personID int DEFAULT_AUTOINCREMENT,
    firstName varchar (30) NOT_NULL,
    lastName varchar (30) NOT_NULL,
    constraint pk_name primary_key (personID)
) 
;
create table addresses (
    addressID int DEFAULT_AUTOINCREMENT,
        personID int NOT_NULL,
    address1 varchar (50),
    address2 varchar (50),
    city varchar (30),
    state varchar (2),
    zipcode varchar (10),
    constraint fk_addresses foreign_key (personID)
        references names (personID),
    constraint pk_addresses primary_key (addressID)
) 
;
create table phoneNumbers (
    phoneID int DEFAULT_AUTOINCREMENT,
        personID int NOT_NULL,
    phoneNumber varchar (20),
    constraint fk_phoneNumbers foreign_key (personID)
        references names (personID),
    constraint pk_phoneNumbers primary_key (phoneID)
) 
;
create table emailAddresses (
    emailID int DEFAULT_AUTOINCREMENT,
        personID int NOT_NULL,
    emailAddress varchar (50),
    constraint fk_emailAddresses foreign_key (personID)
        references names (personID),
    constraint pk_emailAddresses primary_key (emailID)
) 
;

Could anyone please point me in the right direction, where am I going wrong. Thanks.

Upvotes: 0

Views: 139

Answers (3)

Ronald C
Ronald C

Reputation: 1

I am not an expert in php but i also experience this kind of problem . what i did was : 1. export the tables using phpmyadmin. 2. copy the exported tables inside the c:\xampp\mysql\bin DIRECTORY 3. go to COMMAND PROMPT and type 4. cd\ 5. cd c:\xampp\mysql\bin 6. execute this simple command
mysql -u root -p newdatabase < names.sql 

       note : newdatabase is DATABASE_NAME
              names.sql is table.sql that you exported from your database.

i hope it can help you.

Upvotes: 0

Hamed Kamrava
Hamed Kamrava

Reputation: 12847

There are some issues with your code. You have to use correct MySQL key words instead :

        Wrong          |     Correct
=========================================
DEFAULT_AUTOINCREMENT ===> AUTO_INCREMENT
NOT_NULL              ===> NOT NULL
FOREIGN_KEY           ===> FOREIGN KEY
PRIMARY_KEY           ===> PRIMARY KEY

It should be like this :

USE myAddressBook
;

CREATE TABLE NAMES (
    personID INT AUTO_INCREMENT,
    firstName VARCHAR (30) NOT NULL,
    lastName VARCHAR (30) NOT NULL,
    CONSTRAINT pk_name PRIMARY KEY (personID)
) 
;
CREATE TABLE addresses (
    addressID INT AUTO_INCREMENT,
        personID INT NOT NULL,
    address1 VARCHAR (50),
    address2 VARCHAR (50),
    city VARCHAR (30),
    state VARCHAR (2),
    zipcode VARCHAR (10),
    CONSTRAINT fk_addresses FOREIGN KEY (personID)
        REFERENCES NAMES (personID),
    CONSTRAINT pk_addresses PRIMARY KEY (addressID)
) 
;
CREATE TABLE phoneNumbers (
    phoneID INT AUTO_INCREMENT,
        personID INT NOT NULL,
    phoneNumber VARCHAR (20),
    CONSTRAINT fk_phoneNumbers FOREIGN KEY (personID)
        REFERENCES NAMES (personID),
    CONSTRAINT pk_phoneNumbers PRIMARY KEY (phoneID)
) 
;
CREATE TABLE emailAddresses (
    emailID INT AUTO_INCREMENT,
        personID INT NOT NULL,
    emailAddress VARCHAR (50),
    CONSTRAINT fk_emailAddresses FOREIGN KEY (personID)
        REFERENCES NAMES (personID),
    CONSTRAINT pk_emailAddresses PRIMARY KEY (emailID)
) 
;

Upvotes: 2

aweis
aweis

Reputation: 5596

Try to change all the DEFAULT_AUTOINCREMENT to AUTO_INCREMENT and all the NOT_NULL to NOT NULL and primary_key to primary key and foreign_key to foreign key

In general it is because the code does not match the syntax supported by MySQL.

Upvotes: 1

Related Questions