2

I am trying to create a database as portrayed in the following diagram:

enter image description here

This is the SQL-DDL code I have written:

CREATE TABLE Publishers
(
    PublisherName      VARCHAR(50)     NOT NULL,
    PublisherAddress   VARCHAR(50)     NOT NULL,
    PublisherPhone     VARCHAR(50)     NOT NULL,
    PublisherEmail     VARCHAR(50)     NOT NULL,

    CONSTRAINT pk_Publishers PRIMARY KEY (PublisherName)     
);

CREATE TABLE WritingGroup
(
    GroupName    VARCHAR(15)     NOT NULL,
    HeadWriter   VARCHAR(15)     NOT NULL,
    YearFormed   CHAR(4),  
    Subject      VARCHAR(15)     NOT NULL,

    CONSTRAINT pk_WritingGroup PRIMARY KEY (GroupName)
);

CREATE TABLE Books
(
    GroupName        VARCHAR(15)     NOT NULL,
    BookTitle        VARCHAR(50)     NOT NULL,
    PublisherName    VARCHAR(50)     NOT NULL,
    YearPublished    CHAR(4)         NOT NULL,  
    NumberPages      INTEGER         NOT NULL,

    CONSTRAINT pk_Books               PRIMARY KEY (GroupName,BookTitle),
    CONSTRAINT books_pub_fk1          FOREIGN KEY (PublisherName) REFERENCES Publishers (PublisherName),
    CONSTRAINT WritingGroups_Books_fk FOREIGN KEY (GroupName) REFERENCES WritingGroup(GroupName)
);


INSERT INTO publishers VALUES('P01','12345 h st ','+1 215 837 0825','e1@gmail.com');
INSERT INTO publishers VALUES('P02','6789 s st ','+1 215 967 9719','e2@gmail.com');

INSERT INTO books VALUES('GroupName1','title 1 ','P1','1994',50);

SELECT * FROM books

However when running the query I get the following error message:

[Exception, Error code 30,000, SQLState 23505] The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'PK_PUBLISHERS' defined on 'PUBLISHERS'.
Line 38, column 1

Can anybody see where the mistake is? This is my first time writing a database and I really dont see where I am going wrong. Thanks in advance.

1

The problem is in the books insert

INSERT INTO books VALUES('GroupName1','title 1 ','P1','1994',50);

You have a foreign key constraint, books_pub_fk1, referencing publishers, but you don't have a publisher P1, it is P01.

This is for referential integrity.

You will also have the same problem once this is fixed with WritingGroups_Books_fk as you don't have a group record in WritingGroup named GroupName1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.