0

When create table have error "Incorrect syntax near ','." I cant see this error in code. Please point out this error.

CREATE TABLE books(
id INT NOT NULL IDENTITY PRIMARY KEY,
author VARCHAR(150) NOT null,
date DATETIME NOT null,
city VARCHAR(50) NOT null,
publishing VARCHAR(50) NOT null,
udc INT NOT null,
quantity INT NOT null,
inventory_numbers INT NOT NULL PRIMARY KEY)

CREATE TABLE systematic_catalog(
id INT NOT NULL IDENTITY PRIMARY KEY,
udc_id INT FOREIGN KEY REFERENCES books(udc),
knowledge_area VARCHAR)

CREATE TABLE issued_books(
date_issued DATETIME,
inventory_numbers_id INT FOREIGN KEY REFERENCES books(inventory_numbers))

CREATE TABLE readers(
id INT NOT NULL IDENTITY PRIMARY KEY, 
last_name VARCHAR CONSTRAINT,
first_name VARCHAR CONSTRAINT,
middle_name VARCHAR,
phone_number INT(11),
address VARCHAR,
ticket_number INT CONSTRAINT,
date_registration DATETIME,
date_reregistratiom DATETIME,
issued_books_id FOREIGN KEY REFERENCES issued_books(inventory_numbers_id))
Thom A
  • 88,727
  • 11
  • 45
  • 75
tera1004
  • 13
  • 1
  • 3
    `last_name VARCHAR CONSTRAINT,`? `CONSTRAINT` *what*? – Thom A May 26 '21 at 12:20
  • 4
    Also [Bad Habits to Kick : Declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length). Names and addresses are longer than 1 character. – Thom A May 26 '21 at 12:21
  • 2
    `phone_number INT(11)` is also not valid syntax; `int` does not have a scale property. – Thom A May 26 '21 at 12:22
  • 3
    Furthermore you have two primary keys in your books table. If you need a combined key, you have to define it as such: https://stackoverflow.com/questions/3922337/how-to-create-composite-primary-key-in-sql-server-2008/3922359 – derpirscher May 26 '21 at 12:24
  • @Larnu, https://en.wikipedia.org/wiki/List_of_short_place_names – jarlh May 26 '21 at 12:26
  • 1
    And yet all the users whom have participated in the conversionation prove my point with their aliases being more than 1 character, @jarlh (or should that be "@j"?). :) – Thom A May 26 '21 at 12:27
  • And your `FOREIGN KEY REFERENCES books(udc)` won't work because this column doesn't have a unique constraint. Same for `FOREIGN KEY REFERENCES issued_books(inventory_numbers_id)` – derpirscher May 26 '21 at 12:27
  • @larnu my mistake about the "```CONSTRAINT ```". fixed it on ```unique```. about "Names and addresses are longer than 1 character. ". I can use `not null`, right? – tera1004 May 26 '21 at 12:36
  • last_name VARCHAR CONSTRAINT, here is your incorrect syntax. You cannot use Constraint here – Gudwlk May 26 '21 at 12:54

1 Answers1

0

  1. You cannot add multiple primary keys to the table, either composite ey (combination of two fields) or Unique constraint can be added instead.

    CREATE TABLE books(
id INT NOT NULL IDENTITY PRIMARY KEY,
author VARCHAR(150) NOT null,
date DATETIME NOT null,
city VARCHAR(50) NOT null,
publishing VARCHAR(50) NOT null,
udc INT NOT null,
quantity INT NOT null,
inventory_numbers INT NOT NULL PRIMARY KEY)

this is an error, you cannot ass multiple primary keys. Instead you can add Unique constraint Correction would be,

         CREATE TABLE books(
             id INT NOT NULL IDENTITY PRIMARY KEY,
             author VARCHAR(150) NOT null,
             date DATETIME NOT null,
             city VARCHAR(50) NOT null,
             publishing VARCHAR(50) NOT null,
             udc INT NOT null Unique, --this is used to create the second table
             quantity INT NOT null,
             inventory_numbers INT NOT NULL Unique ) 

            CREATE TABLE systematic_catalog(
            id INT NOT NULL IDENTITY PRIMARY KEY,
            udc_id INT FOREIGN KEY REFERENCES books(udc), --Referenced column needs to be unique
            knowledge_area VARCHAR)


             CREATE TABLE issued_books(
             date_issued DATETIME,
             inventory_numbers_id INT FOREIGN KEY REFERENCES books(inventory_numbers))

              CREATE TABLE readers(
              id INT NOT NULL IDENTITY PRIMARY KEY, 
              last_name VARCHAR (255),
              first_name VARCHAR (255),
              middle_name VARCHAR(255),
              phone_number INT,
              [address] VARCHAR(255),
              ticket_number INT ,
              date_registration DATETIME,
              date_reregistratiom DATETIME)

You cannot use a foreign key of another table as a foreign key for some other table. You need to read more on table, Primary key constraints and foreign key referencing.

This cannot be done. you are trying to refer to the foreign key of issued_books table as a foreign key in readers table, which is wrong.

      issued_books_id INT FOREIGN KEY REFERENCES issued_books(inventory_numbers_id))

I hope this explanation will give you a better understanding of errors. There were synatx errors as well as wrong use of the SQL table creation steps. I have added the corrected code for you. Feel free to contact any time.

Gudwlk
  • 1,177
  • 11
  • 11