Trouble Shooting Syntax Errors in Create Table Statement - Spring 2025

Guide and Check List for Testing and Debugging CREATE TABLE Statements

  

Code and Script Testing is an important component of system development. Both programming code testing and Script Testing are Essential.  Basic SQL falls in the category of Script Testing. This announcement will briefly review some syntax errors that students typically have when writing SQL scripts, specifically when they are writing SQL scripts for the CREATE TABLE Statements. The announcement also includes some possible solutions to these problems.  

 

Sequence and Placement of the CREATE TABLE Statements

To implement a relational database, a series of CREATE TABLE Statements containing Primary Key and Foreign Key Constraints must be executed in a specific sequential order without any syntax errors.  To be successful in the CREATE TABLE and INSERT INTO Statement Assignments, students need to be able to write a sequence of CREATE TABLE Statements that do not contain any syntax errors or logical errors. This announcement contains a section that lists common syntax and logical errors when implementing and testing CREATE TABLE Statements in a Relational Database using standard SQL.

 

 

 

Getting Additional Support Correcting SQL Syntax Errors

Students having difficulties with the segments on writing CREATE TABLE and INSERT INTO Statement should schedule a Zoom Conference with a CCP Tutor in the Learning Lab or schedule a Zoom meeting with me as soon as possible (ASAP).

  

Some Basic Code Testing Principles

After reviewing a sample of some of the recently submitted SQL scripts on writing CREATE TABLE Statements for the first practice assignment to write CREATE TABLE Statements, I noticed a few common issues. 

 

Students should never submit Scripts with known Syntax Errors

Another problem that I noticed was that some students are submitting SQL scripts with known syntax errors.  This should never occur for students who do not wait until the last minute to plan and write the SQL Statements that respond to assignment requests.  Students can and should test their queries before submitting them.  Syntax errors can be corrected through interaction with a CCP Tutor in the CCP Learning Lab or Interaction with me during my scheduled office hours, or through interactive email exchanges.

 

 

CREATE DATABASE Statement and USE Statement

A typical problem is that students forget to make sure that there is a CREATE DATABASE statement and a USE Statement at the top of the SQL Script. This should always occur first.  Each Statement must have a semicolon at the end of the statement.  

 

DROP DATABASE Statement

It is preferable to also include a DROP DATABASE statement at the beginning of the SQL Script containing CREATE TABLE statements. This will make sure that you clear the older version of the database when adding and updating to a newer version.

  

Test One CREATE TABLE Statement at a Time

 

It is best to test one statement at a time. Testing and debugging code is an iterative process.  It may take several passes or sequences of testing and debugging before the entire assignment is fully tested and all the code runs as expected.  Do not wait until the last minute to begin this process.

 

 When testing SQL Script, I always recommend testing one statement at a time or a block of statements at a time.  This is especially true if some of the following statements that you will write follow a similar pattern to the previous statement or follow a pattern of statements that have been written and tested.  This permits you to follow the same pattern of successfully tested and debugged code.  In many situations, this can be done by copying and pasting portions of the successfully tested code into the new code that you are writing.  You can consider this a form of code reuse, which is a component of software engineering principles.

 

Do Not Submit SQL Script with Known Errors

If you attempted to test your queries, and you submitted your code with errors, this means that you were aware that these queries contained syntax errors before submitting them for grading.  Submitting SQL Script with known errors is not a recommended process. Students should seek assistance when they cannot debug a syntax error.  This can be done through me by email or by a Zoom Meeting during my scheduled office hours.  This can also be done by scheduling a live or Zoom Meeting with a CCP Tutor in the CCP Learning Lab  

 

 

 

 

Comment Code with Syntax Errors

  If for some reason students are not able to remove a syntax error before the deadline for submitting the assignment, the code should be commented on, so its errors do not create cascading errors in other SQL Statements that follow it. Make sure to use comment indicators correctly

 

  Students should comment on the code that they were not able to troubleshoot.  This will send a signal to DBMS to ignore the code that has comment indicators in front of it and not interpret the code as an executable script. This will permit the SQL Statements that are correct to run sequentially and continuously.  SQL Script running continuously and without syntax errors is a major requirement in this course. There are tutorials in the Introduction Module on commenting on SQL scripts correctly.

 

**** This is very Important ****** 

Many times, an SQL Statement that has a syntax error will cause cascading errors in the SQL Statements that follow it.  Therefore, you want to comment on any SQL Statement containing syntax errors.  You can always go back and correct the issues later.  However, the important thing is to submit code that does not generate syntax errors. The SQL script must run continuously when copying and pasting the text into the command line of the DBMS or using the SOURCE Statement to import the script directly into the command line for execution.

   When there is an error that you cannot resolve, Get Support

If you are aware of issues before submitting assignment material ahead of time, there are a couple of avenues to pursue to assist you. One path is to contact a tutor in the college's Learning Lab for Assistance. The Learning Lab will schedule a Zoom Video Conference between the students and one of their tutoring staff members supporting the course. Another path is to contact your instructor and schedule a Zoom Video Conference with your professor to review your issues.  If scheduling a Zoom Meeting with your professor, this should be done at least 24 hours before the Due Date of the assignment.

  

When Testing Your CREATE TABLE SQL Script, Some Basic Syntax Errors can be in CREATE TABLE Statements To Look For.

 

Make sure that you test your SQL script by copying and pasting your SQL script from your text file into the MariaDB command line or one of the online SQL Interpreters such as Paiza.io or Onecompiler.com. Here are some basic tips to troubleshoot some common syntax errors that beginners and even professionals may encounter in a CREATE TABLE Statement.

 

• CHECK for COMMAS While testing your scripts. Make sure that all attribute declarations for the create table statement ends with a comma.
   
• No Comma in the LAST DECLARATION in the CREATE TABLE Statement. Make sure that the last constraint declaration for your create table statements does not end with a comma.  Commas indicate that there is another declaration.  In a CREATE TABLE Statement, if there is not a declaration following the last constraint declaration, do not include a comma in that declaration.
  
  
• BALANCED PARENTHESES Make sure that there is a closing right parenthesis and the end of the CREATE TABLE Statement. Additionally, make sure that there is starting and ending parentheses for the attributes in CONSTRAINT Statements. Make sure there is starting and ending parentheses for the attribute Data Type Declaration lengths such as VARCHAR and CHAR.
  
  
• ENDING SEMICOLON Make sure that there is a semicolon following the closing right parenthesis of the CREATE TABLE Statement

 

 

 

 

 

Referential Integrity Syntax Errors in CREATE TABLE Statements

 

RUN CREATE TABLE Statements without FOREIGN KEYS FIRST

The Order and Sequence which you execute the CREATE TABLE Statements is very important. Create the tables on the 1 side of the relationships first.

  

1-M Relationship Referential Integrity Syntax Errors

Referential Integrity problems are also a common syntax error that one can get when writing a sequence of CREATE TABLE Statements.  Many times, this occurs when the order of the CREATE TABLE Statements is not in the proper sequence. This is when the cardinality that was illustrated in the ER Diagram becomes helpful in solving problems during the implementation phase of database development.  The CREATE TABLE Statements representing the Entities in the ER Diagram that are on the 1 side of the 1-M relationships in the ER Diagram must be placed in the SQL script before the CREATE TABLE Statements for the Entities in the ER Diagram that are on the many sides of the 1-M relationships. This order must be maintained in the sequence of CREATE TABLE statements. Next week, you will also see that the order of the INSERT INTO Statements is also important. 

 

 

If the CREATE TABLE representing the Entity on the many sides in the ER Diagram is placed before the CREATE TABLE Statements of the Entities on the 1 side in the ER Diagram, the DBMS will generate a referential integrity syntax error and will not allow the table to be created.  It is enforcing referential integrity. The CREATE TABLE Statement attempts to reference and link a Foreign Key to a Primary Key in another table that does not exist.

 

  M-N Relationship Referential Integrity Syntax Errors 

Referential Integrity Syntax Errors can also occur in CREATE TABLE Statement sequences of M-N Relationships.  In an M-N relationship, there are two different 1-M relationships.  When the relationship between two entities is M_N, the Relationship in the ER Diagram becomes a table during translation.  This table is referred to as the mapping table in many circles, it is also referred to as an association or bridge table.  Each of the Entities on one side of the relationship in the ER Diagram maps to the mapping table/relationship/ associative bridge table on the many sides of the ER Diagram by placing their primary keys on one side of the ER Diagram as foreign keys in the mapping table on the many sides. 

 

 

 

When running and testing the script, the CREATE TABLE Statements representing the Entities on the 1 side in the ER Diagram must be placed before the CREATE TABLE Statements representing the mapping table/relationship on the many sides of the ER Diagram. This means that the Entities connected to the Relationship Table must be created before the CREATE TABLE Statement for the Relationship, association, bridge, and mapping Table. This order must be maintained in the sequence of CREATE TABLE statements in the SQL Script and the order of the INSERT INTO Statements. 

 

 When the CREATE TABLE representing the mapping table/relationship table on the many sides of the ER Diagram is placed before the CREATE TABLE Statements of the Entities on the 1 side of the ER Diagram, the DBMS generates a syntax error and will not allow the table to be created.  It is enforcing referential integrity. 

 

 1-1 Relationship Referential Integrity Syntax Errors

 

Referential Integrity Syntax Errors can also occur in CREATE TABLE Statement sequences of 1-1 Relationships.  In 1-1 Relationships, one of the two Entities in the ER Diagram must be selected as the primary key table during implementation and the other Entity must be selected as the Foreign Key Table during implementation.

 

 Once again, the order in which CREATE TABLE Statements are placed is important to success. In 1-1 Relationships. The CREATE TABLE Statement representing the Entity containing the Primary Key of the relationship in the ER Diagram must be placed before the CREATE TABLE Statement representing the Entity containing the Foreign Key of the relationship in the ER Diagram.  This order must be maintained in the sequence of CREATE TABLE statements in the SQL Script and the order of the INSERT INTO Statements. 

 

If the CREATE TABLE representing the Entity containing the Foreign Key is placed before the CREATE TABLE Statements of the Entities' Primary Key that the Foreign Key is referencing, the DBMS will generate a syntax error and will not allow the table to be created.  It is enforcing referential integrity.

 

Data Type Compatibility

The Data Type and Data Type Size of Foreign Key Attribute in a Foreign Key CONSTRAINT clause must be identical to the Primary Key.  If they are not identical, the SQL Interpreter will generate a Foreign Key Constraint syntax error.

 

Foreign Keys must refer to an attribute that has been defined as a Primary Key.  If the foreign key in the Foreign Key Constraint clause does not reference an attribute that has been defined as a Primary Key, the SQL Interpreter will generate a Foreign Key Constraint syntax error.

If there is more than one Foreign Key defined within a single table, make sure that Foreign Key Constraint names are different in each foreign key declaration.  For Example, you cannot have two foreign key Constraint names such as Orders_FK1 and Orders_FK1 in the same table. Foreign Key names should have numerated suffixes. 

 

Other Types of Grading and Assignment Scoring Issues and Solutions 

A typical submission issue where students may lose points for the assignment is that the submission does not include the required support documentation.  Remember to read the assignment requirements to make sure that you include all the required files and material.  Remember to review the list of submitted files once the submission confirmation dialogue appears.  It will always contain the date and time of the submission.  The window will contain the list of submitted attached files.  Check to make sure a file was not omitted from the submission.  

 

 

 

All SQL Script For an Assignment Must Be Contained In A Single Text File With An SQL Extension.

 

Another requirement is that all SQL scripts must be placed in a single SQL text file with an SQL extension. Some students include multiple SQL Files in the submission.  All Assignment Scripts must be placed into a Single SQL Text file with an SQL extension.  Students should have a checklist to make sure that all components of the SQL Assignment are placed into a single SQL Text File for submission. 

 

 

 Code Must Execute Continuously

 One of the main requirements for all SQL assignments is that the script executes continuously. It is every student’s responsibility to test and debug their SQL script before submission. Syntax errors in one section of code can cause cascading errors in the text that follows it.  There are times when a syntax error cannot be removed on time. If you are unable to remove a syntax error on time for assignment submission, comments must be used to block out the syntax error so that the surrounding code can run continuously without errors. A common error that causes a cascading error is that students forget to include the CREATE DATABASE and USE statement at the beginning of the SQL script.

 

Save and Update Script Before Submitting: Students should save the latest modifications to the SQL script before submitting the script for assessment.

 

Test Script Before Submitting: Syntax errors should be discovered, traced, and debugged with the effort and utilization of support channels such as tutoring in the Learning Lab, Peer Collaboration, or Interaction with your Professor.  Make sure that the SQL scripts run continuously before submitting the SQL Text File. 

 

 

A Database must be in USE for a CREATE TABLE Statement to Work

The SQL Script File submitted does not contain a CREATE DATABASE Statement or USE Database Statement.  CREATE TABLE Statements will generate Syntax Errors if a Database is not currently in use.  You must have a CREATE DATABASE Statement and a USE database statement before the CREATE TABLE statements.

 

All Attribute Declarations Must Have an Ending Comma

All your attribute declarations in your CREATE TABLE Statements are missing commas. 

 

If none of the statements in your submission work. Below is a list of primary reasons why

 

• You are missing a CREATE Database Statement at the beginning of your SQL Script.
• You are missing a USE Database Statement at the beginning of the script.
• You have semicolons at the beginning of each of your CREATE TABLE Statements. A semicolon should only go at the end of the CREATE TABLE statement in SQL.

 

You should test your SQL script before submitting it for grading.

 

At Least one of the CREATE TABLE Statements must contain a functional Foreign Key Constraint Clause

 

If none of your CREATE TABLE Statements contain Foreign Key declarations, this will be a problem. One whole function of a Relational Database is to establish Relationships between tables and enforce those Relationships through Primary Key Foreign Key Connections.

 

Foreign Key Constraint Clauses must REFERENCE an existing table and Attributes.

 

All Foreign Key Constraints must refer to a Table that currently exists.  Place the CREATE TABLE Statements for the tables on the 1 side of 1_M Relationships before the CREATE TABLE Statements for the tables on the Many sides of the 1_M Relationships. This will ensure that the tables that FOREIGN KEY CONSTRAINTS are REFERENCING have been created.

 

Additionally, the Foreign Key Constraint Clause must reference a Foreign Key Attribute Name in the table containing the constraint clause and a Primary Key Attribute Name defined in another table.