CODEWITHSUNDEEP
mysqldatabaseoracle-database
VBNewbie
VBNewbie

Reputation: 1

CONSTRAINT statements in my table creations

I'm looking for some help with these CONSTRAINT statements in my table creations. It looks like something simple but I'm missing it for some reason. When I try to change to add open or close parentheses within different areas of the statement, nothing seems to change.

Error Code: 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 'REFERENCES (DISTRIBUTOR.DISTRIBUTOR_ID), CONSTRAINT VIDEO_DVD_FK2 FOREIGN KEY R' at line 15

Error Code: 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 'CONSTRAINT DISTRIBUTOR_PK PRIMARY KEY (DISTRIBUTOR_ID), CONSTRAINT DISTRIBUTOR_' at line 11

Error Code: 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 'REFERENCES (DISTRIBUTOR.DISTRIBUTOR_ID))' at line 25

Error Code: 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 'REFERENCES (VIDEO_DVD.VIDEO_DVD_ID))' at line 11

CREATE TABLE VIDEO_DVD(

VIDEO_DVD_ID   VARCHAR(100),
DISTRIBUTOR_ID   varchar(100),
MOVIE_ID   varchar(100),
CUSTOMER_ID   varchar(100),
FINE_AMT   numeric(14,0)   CHECK (FINE_AMT > 0),
DAMAGE_CHARGES   numeric(14,0)   CHECK (DAMAGE_CHARGES > 0),
TYPE   CHAR(1)   check(TYPE IN ('V','D')),
LEASE_DATE   DATE,   
RETURN_DATE   DATE   NOT NULL,

CONSTRAINT VIDEO_DVD_PK PRIMARY KEY (VIDEO_DVD_ID),

CONSTRAINT VIDEO_DVD_FK1 FOREIGN KEY REFERENCES   (DISTRIBUTOR.DISTRIBUTOR_ID),

CONSTRAINT VIDEO_DVD_FK2 FOREIGN KEY REFERENCES (MOVIE.MOVIE_ID),

CONSTRAINT VIDEO_DVD_FK3 FOREIGN KEY REFERENCES (CUSTOMER.CUST_ID));
CREATE TABLE DISTRIBUTOR(

DISTRIBUTOR_ID   VARCHAR(100) ,

MOVIE_ID   VARCHAR(100),

VIDEO_DVD_ID   VARCHAR(100),

NAME   VARCHAR(1000)   UNIQUE NOT NULL

CONSTRAINT DISTRIBUTOR_PK PRIMARY KEY (DISTRIBUTOR_ID),

CONSTRAINT DISTRIBUTOR_FK1 FOREIGN KEY REFERENCES (MOVIE.MOVIE_ID),

CONSTRAINT DISTRIBUTOR_FK2 FOREIGN KEY REFERENCES (VIDEO_DVD.VIDEO_DVD_ID));
CREATE TABLE MOVIE(

MOVIE_ID   VARCHAR(100),

DIRECTOR   VARCHAR(100) NOT NULL,

NAME   VARCHAR(100) UNIQUE NOT NULL,

TYPE   CHAR(1)   CHECK (TYPE IN ('C','H','S')),

DISTRIBUTOR_ID VARCHAR(100),

ACTOR_NAME   VARCHAR(100),  

LENGTH   NUMERIC(14,0)   CHECK (LENGTH > 0),

AWARDS   VARCHAR(1000),

RATINGS   NUMERIC(2,0)   CHECK (RATINGS IN (1,2,3,4,5,6,7,8,9,10)),

RELEASE_YEAR   NUMERIC(4,0),

CONSTRAINT MOVIE_PK PRIMARY KEY (MOVIE_ID),

CONSTRAINT MOVIE_FK FOREIGN KEY REFERENCES (DISTRIBUTOR.DISTRIBUTOR_ID));
CREATE TABLE CUSTOMER(

CUST_ID   VARCHAR(100),
VIDEO_DVD_ID   VARCHAR(100),
STREET   VARCHAR(1000),
ZIPCODE   NUMERIC(6,0),
ACC_NO   VARCHAR(20) UNIQUE NOT NULL,

CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUST_ID),

CONSTRAINT CUSTOMER_FK FOREIGN KEY REFERENCES (VIDEO_DVD.VIDEO_DVD_ID));

I'm using MySQL 5.7...

I was able to clear a few errors by deleting the comma at the end of the statements so I hope this will still work. I am still having these errors tho..

14:41:47 CREATE TABLE VIDEO_DVD( VIDEO_DVD_ID VARCHAR(100), DISTRIBUTOR_ID varchar(100), MOVIE_ID varchar(100), CUSTOMER_ID varchar(100), FINE_AMT numeric(14,0) CHECK (FINE_AMT > 0), DAMAGE_CHARGES numeric(14,0) CHECK (DAMAGE_CHARGES > 0), TYPE CHAR(1) check(TYPE IN ('V','D')), LEASE_DATE DATE, RETURN_DATE DATE NOT NULL, CONSTRAINT VIDEO_DVD_PK PRIMARY KEY (VIDEO_DVD_ID), CONSTRAINT VIDEO_DVD_FK1 FOREIGN KEY (DISTRIBUTOR_ID) REFERENCES DISTRIBUTOR.DISTRIBUTOR_ID) CONSTRAINT VIDEO_DVD_FK2 FOREIGN KEY REFERENCES (MOVIE.MOVIE_ID) CONSTRAINT VIDEO_DVD_FK3 FOREIGN KEY REFERENCES (CUSTOMER.CUST_ID)) Error Code: 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 'CONSTRAINT VIDEO_DVD_FK2 FOREIGN KEY REFERENCES (MOVIE.MOVIE_ID) CONSTRAINT VID' at line 17 0.016 sec

14:41:47 CREATE TABLE DISTRIBUTOR( DISTRIBUTOR_ID VARCHAR(100) , MOVIE_ID VARCHAR(100), VIDEO_DVD_ID VARCHAR(100), NAME VARCHAR(1000) UNIQUE NOT NULL CONSTRAINT DISTRIBUTOR_PK PRIMARY KEY (DISTRIBUTOR_ID), CONSTRAINT DISTRIBUTOR_FK1 FOREIGN KEY REFERENCES (MOVIE.MOVIE_ID), CONSTRAINT DISTRIBUTOR_FK2 FOREIGN KEY REFERENCES (VIDEO_DVD.VIDEO_DVD_ID)) Error Code: 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 'CONSTRAINT DISTRIBUTOR_PK PRIMARY KEY (DISTRIBUTOR_ID), CONSTRAINT DISTRIBUTOR_' at line 11 0.000 sec

14:41:47 CREATE TABLE MOVIE( MOVIE_ID VARCHAR(100), DIRECTOR VARCHAR(100) NOT NULL, NAME VARCHAR(100) UNIQUE NOT NULL, TYPE CHAR(1) CHECK (TYPE IN ('C','H','S')), DISTRIBUTOR_ID VARCHAR(100), ACTOR_NAME VARCHAR(100), LENGTH NUMERIC(14,0) CHECK (LENGTH > 0), AWARDS VARCHAR(1000), RATINGS NUMERIC(2,0) CHECK (RATINGS IN (1,2,3,4,5,6,7,8,9,10)), RELEASE_YEAR NUMERIC(4,0), CONSTRAINT MOVIE_PK PRIMARY KEY (MOVIE_ID), CONSTRAINT MOVIE_FK FOREIGN KEY (MOVIE_ID) REFERENCES DISTRIBUTOR.DISTRIBUTOR_ID) Error Code: 1215. Cannot add foreign key constraint 1.109 sec

I am down to my last 3 errors that I still cannot get to work. Any help would be greatly appreciated! Thanks!

CONSTRAINT VIDEO_DVD_FK2 FOREIGN KEY (MOVIE_ID) REFERENCES (MOVIE.MOVIE_ID),

Error Code: 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 'CONSTRAINT VIDEO_DVD_FK2 FOREIGN KEY (MOVIE_ID) REFERENCES (MOVIE.MOVIE_ID), CO' at line 17

CONSTRAINT DISTRIBUTOR_PK PRIMARY KEY (DISTRIBUTOR_ID),

Error Code: 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 'CONSTRAINT DISTRIBUTOR_PK PRIMARY KEY (DISTRIBUTOR_ID), CONSTRAINT DISTRIBUTOR_' at line 11

CREATE TABLE MOVIE(  MOVIE_ID   VARCHAR(100),  DIRECTOR   VARCHAR(100) NOT NULL,  NAME   VARCHAR(100) UNIQUE NOT NULL,  TYPE   CHAR(1)   CHECK (TYPE IN ('C','H','S')),  DISTRIBUTOR_ID VARCHAR(100),  ACTOR_NAME   VARCHAR(100),    LENGTH   NUMERIC(14,0)   CHECK (LENGTH > 0),  AWARDS   VARCHAR(1000),  RATINGS   NUMERIC(2,0)   CHECK (RATINGS IN (1,2,3,4,5,6,7,8,9,10)),  RELEASE_YEAR   NUMERIC(4,0),  CONSTRAINT MOVIE_PK PRIMARY KEY (MOVIE_ID),  CONSTRAINT MOVIE_FK FOREIGN KEY (DISTRIBUTOR_ID) REFERENCES DISTRIBUTOR.DISTRIBUTOR_ID)

Error Code: 1215. Cannot add foreign key constraint 0.453 sec

Upvotes: 0

Views: 176

Answers (1)

nemetroid
nemetroid

Reputation: 2159

You haven't written the column name of the foreign keys. You need to write e.g.:

CONSTRAINT VIDEO_DVD_FK1 FOREIGN KEY (DISTRIBUTOR_ID) REFERENCES (DISTRIBUTOR.DISTRIBUTOR_ID),

Upvotes: 0

Related Questions