CODEWITHSUNDEEP
mysqlsqldatabase
user847623
user847623

Reputation:

SQL Structure help and query

I can not find out what is wrong with my SQL Query:

CREATE TABLE Product (
                      productID int NOT NULL, 
                      name varchar(255) NOT NULL, 
                      price int(255), 
                      PRIMARY KEY (productID) 
                      )

CREATE TABLE User ( 
                   userID int NOT NULL, 
                   PRIMARY KEY (userID) 
                   )

CREATE TABLE Purchased ( 
                   productID int NOT NULL, 
                   userID varchar(255) NOT NULL, 
                   date date(255), NOT NULL, 
                   FOREIGN KEY (productID) REFERENCES Product(productID) FOREIGN KEY (userID) REFERENCES User(userID) 
                   )

Please can someone help

Upvotes: 0

Views: 62

Answers (4)

user3190254
user3190254

Reputation:

In your query the problem is in date type column so no need to declare the date as variable because it is keyword in sql.

     1.Date is a keyword
     2.No need to size for date

Upvotes: 1

G one
G one

Reputation: 2729

There are some syntax error in your create table statement.

  • Date is a keyword so not a good practice to use it.
  • User_id is int in your USER table and in purchased table you are making it varchar
  • For date datatype no need to specify the number of characters.

The correct statement is

CREATE TABLE purchased 
  ( 
     productid INT NOT NULL, 
     userid    INT NOT NULL, 
     date1     DATE NOT NULL, 
     FOREIGN KEY (productid) REFERENCES product(productid), 
     FOREIGN KEY (userid) REFERENCES USER(userid) 
  )

SQL Fiddle

Upvotes: 0

Nagaraj S
Nagaraj S

Reputation: 13484

Use date date NOT NULL 

CREATE TABLE Product (productID int NOT NULL, name varchar(255) NOT NULL, price int(255), PRIMARY KEY (productID));

CREATE TABLE User ( userID int NOT NULL, PRIMARY KEY (userID) );

CREATE TABLE Purchased (productID int NOT NULL, userID int NOT NULL , date date NOT NULL,
                    FOREIGN KEY (productID) REFERENCES Product(productID),
                    FOREIGN KEY (userID) REFERENCES User(userID))

SQl fiddle

Upvotes: 0

Incognito
Incognito

Reputation: 3094

To start with, you have a syntax error in your third CREATE TABLE statement, where you have specified a comma before NOT NULL constraint and a missing comma before second foreign key definition.

Another thing to note is, you are not supposed to specify any parameter to DATE data type, like you have specified.

EDIT: The data type of userID in this table needs to be same as the data type of the user table for the foreign key to work.

The correct statement is

CREATE TABLE Purchased (productID int NOT NULL, 
                        userID INT NOT NULL, 
                        date date NOT NULL, 
                        FOREIGN KEY (productID) REFERENCES Product(productID), 
                        FOREIGN KEY (userID) REFERENCES User(userID) 
                        )

If you're getting some other error, please update your question

Upvotes: 1

Related Questions