CODEWITHSUNDEEP
mysqlsql
user962971
user962971

Reputation: 47

Getting error while creating the tables in MySQL

I am trying to run the following SQL script: 

CREATE TABLE part
(Partno    CHAR(4) PRIMARY KEY,
 Partdesc  VARCHAR(20),
 Onhand    INTEGER,
 Partclass CHAR(2) check (Partclass IN ('AP','HW','KI','SP')),
 Unitprice DECIMAL(6,2)
);


CREATE TABLE salesrep
(Srepno     CHAR(3),
 Srepname   VARCHAR(25),
 Srepstreet VARCHAR(30),
 Srepcity   VARCHAR(15) NOT NULL,
 Srepprov   VARCHAR(3) NOT NULL,
 Sreppcode  VARCHAR(6) NOT NULL,
 Totcomm    DECIMAL(8,2),
 Commrate   DECIMAL(3,2),
 CONSTRAINT pkslsrep PRIMARY KEY (Srepno)
);


CREATE TABLE customer
(Custno     CHAR(3),
 Custname   VARCHAR(25) NOT NULL,
 Custstreet VARCHAR(30) NOT NULL,
 Custcity   VARCHAR(15) NOT NULL,
 Custprov   VARCHAR(3) NOT NULL,
 Custpcode  VARCHAR(6) NOT NULL,
 Disc       DECIMAL(3,1),
 Balance    DECIMAL(7,2),
 Credlimit  DECIMAL(5),
 Srepno     CHAR(3),
 CONSTRAINT pkcustno PRIMARY KEY (Custno),
 CONSTRAINT fksrepno FOREIGN KEY (Srepno) REFERENCES salesrep(Srepno)
);


CREATE TABLE orders
(Orderno   CHAR(5) UNIQUE NOT NULL,
 Orderdate DATE,
 Custno    CHAR(3) NOT NULL,
 CONSTRAINT fkordercust FOREIGN KEY (Custno) REFERENCES customer (Custno)
);


CREATE TABLE orderprod
(Orderno    CHAR(5),
 Partno     CHAR(4),
 Orderqty   INTEGER CHECK (Orderqty>0),
 Orderprice DECIMAL(7,2),
 CONSTRAINT pkorderprod PRIMARY KEY (Orderno, Partno),
 CONSTRAINT fkordprdord FOREIGN KEY (Orderno) REFERENCES orders(Orderno),
 CONSTRAINT fkordprdpar FOREIGN KEY (Partno) REFERENCES part(Partno)
);


CREATE TABLE invoice
( Invno     CHAR(6),
  Invdate   DATE,
  Orderno    CHAR(5),
  CONSTRAINT fkinvoice FOREIGN KEY (Orderno) REFERENCES orders(Orderno),
  CONSTRAINT pkinvoice PRIMARY KEY (Invno)
);


CREATE TABLE invprod
( Invno     CHAR(6),
  Partno     CHAR(4),
  Shipqty    INTEGER CHECK (Shipqty>0),
  CONSTRAINT fkinvoice FOREIGN KEY (Invno) REFERENCES invoice(Invno),
  CONSTRAINT fkpart FOREIGN KEY (Partno) REFERENCES part(Partno)
);

DROP TABLE invprod;
DROP TABLE invoice;
DROP TABLE orderprod;
DROP TABLE orders;
DROP TABLE customer;
DROP TABLE salesrep;
DROP TABLE part;*

I am not able to create the table invprod. I am getting the error: 69 Can't create table 'sh.invprod' (errno: 121). What errors am I making in this script?

Upvotes: 0

Views: 183

Answers (1)

Raihan
Raihan

Reputation: 10425

CONSTRAINT fkinvoice is already defined as part of CREATE TABLE invoice. Constraint names should be unique like tables or indexes.

Upvotes: 2

Related Questions