2 Replies - 804 Views - Last Post: 13 February 2016 - 02:43 PM

#1 Skeledrone  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 13-February 16

MS Access SQL problem with tables and foreign key reference

Posted 13 February 2016 - 04:38 AM

Hi,

I'm trying to make a simple SQL program in MS Access 2007 for a university assignment. I'm not allowed to create the table manually or with other programs, I have to do so with SQL.

So I have one query with 2 create table statements each with a primary key, and one with a foreign key trying to link to the other. Whenever I try to click run it says "Syntax error in CREATE TABLE statement" and it points to the CREATE key word of the second tables SQL code.

I tried making a new query and moving the second table into it, but then when I try to run the first it says "Cannot find table or contraint".

Can you please explain both errors to me.


Here's my code for the first try:


CREATE TABLE CasaDois
(
    CompName VARCHAR(50) UNIQUE,
    Valuation NUMBER NOT NULL,
    SectorOfOperation VARCHAR(50) UNIQUE,
    NumEmployees NUMBER,
    FounderCompany VARCHAR(50),
    ProductsSoldByCompany VARCHAR(50) UNIQUE,

    PRIMARY KEY (CompName),
    FOREIGN KEY (FounderCompany) REFERENCES FullName(FounderCompany)

)

CREATE TABLE  FounderCompany
(
    FullName VARCHAR(50) UNIQUE,
    Age NUMBER NOT NULL,
    HighestQualification VARCHAR(50),

    PRIMARY KEY (FullName)
)

;


And here for the second:

Query one:

CREATE TABLE CasaDois
(
    CompName VARCHAR(50) UNIQUE,
    Valuation NUMBER NOT NULL,
    SectorOfOperation VARCHAR(50) UNIQUE,
    NumEmployees NUMBER,
    FounderCompany VARCHAR(50),
    ProductsSoldByCompany VARCHAR(50) UNIQUE,

    PRIMARY KEY (CompName),
    FOREIGN KEY (FounderCompany) REFERENCES FullName(FounderCompany)

)

;


Query two:

CREATE TABLE  FounderCompany
(
    FullName VARCHAR(50) UNIQUE,
    Age NUMBER NOT NULL,
    HighestQualification VARCHAR(50),

    PRIMARY KEY (FullName)
)

;



Any help will be much appreciated as the assignment is due soon

Thanks

This post has been edited by Atli: 13 February 2016 - 05:41 AM
Reason for edit:: Added [code]...[/code] tags.


Is This A Good Question/Topic? 0
  • +

Replies To: MS Access SQL problem with tables and foreign key reference

#2 Atli  Icon User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4238
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: MS Access SQL problem with tables and foreign key reference

Posted 13 February 2016 - 05:48 AM

Moved to the MS Access forum. Access and MySQL are two very different things.

Quote

Whenever I try to click run it says "Syntax error in CREATE TABLE statement" and it points to the CREATE key word of the second tables SQL code.

In your first snippet, you are missing the semi-colon at the end of the first CREATE TABLE statement. Each individual statement needs to be separated from the others, which is what the semi-colon is used for.
CREATE TABLE first (...) ;
CREATE TABLE second (...) ;



Quote

... but then when I try to run the first it says "Cannot find table or contraint".

Your "CasaDois" table is referencing a field in the "FounderCompany" table. Therefore, when you create the "CasaDois" table, the "FounderCompany" must already exist. You can't crate a link to a non-existing table. - So, you need to switch the order of your CRATE TABLE statements.

Also, your REFERENCES clause in the "CasaDois" creation statement is incorrect. It should read: ... REFERENCES tableName(fieldName). You have the field and table names in the wrong places.
Was This Post Helpful? 0
  • +
  • -

#3 Skeledrone  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 13-February 16

Re: MS Access SQL problem with tables and foreign key reference

Posted 13 February 2016 - 02:43 PM

Sorry, I didn't mean to repost that (my first time on the forums :) ).

Anyway, I tried your advice and I added the semi-colon, and fixed the REFERENCES thing, and I still get the same "Syntax error in CREATE TABLE statement" error. The error still points to the CREATE keyword on the second set of code.

Any ideas on what else could be wrong? I am using MS access 2007 if that may be the problem?

Thank you.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1