Duplication Error

I'm currently enrolled in MYSQL class at college. Our teacher thinks that the开发者_Python百科 best way to teach is by letting the students teach themselves. I need to create the table inventory for class but I am having a duplication error. Here is my code.

CREATE TABLE INVENTORY
(BOOK_CODE CHAR(4) PRIMARY KEY,
BRANCH_NUM DECIMAL (2,0) NOT NULL,
ON_HAND DECIMAL (2,0) );

There are only 3 items because the list is very long. If I can understand the concept then it really shouldn't be an issue from there.

INSERT INTO INVENTORY
VALUES
('079X','2','1');
INSERT INTO INVENTORY
VALUES
('079X','3','2');
INSERT INTO INVENTORY
VALUES
('079X','4','3');

The first value Refers to a Book title, followed by branch number, then available books per branch.

I must also be able to see the values, so i can't ignore them. Ex. I need to see them from the select command.

BOOK_CODE is PRIMARY KEY, it need to be unique.

and you try to insert the same value 079X

so to work do something like

INSERT INTO INVENTORY
VALUES
('079X','2','1');
INSERT INTO INVENTORY
VALUES
('080X','3','2');
INSERT INTO INVENTORY
VALUES
('081X','4','3');

read more

A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY. If you do not have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY.

PRIMARY KEY for a table means that there can only be a single entry/row in the table with that specific value (or combination of values).

So insertint '079x' 3 times does not make it unique.

Have a look at Primary Key Definition

Definition: The primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique (such as Social Security Number in a table with no more than one record per person) or it can be generated by the DBMS (such as a globally unique identifier, or GUID, in Microsoft SQL Server). Primary keys may consist of a single attribute or multiple attributes in combination.

Your BOOK_CODE is marked as primary key, so it needs to have a unique value identifying each row.

It sounds like BOOK_CODE does not uniquely identify a row, but the combination of BOOK_CODE and BRANCH_NUM does.

You could then do:

CREATE TABLE INVENTORY
(
BOOK_CODE CHAR(4) ,
BRANCH_NUM DECIMAL (2,0) NOT NULL,
ON_HAND DECIMAL (2,0),
PRIMARY KEY (BOOK_CODE,BRANCH_NUM)
);

(note, It might be more common to have a primary key just be a surrogate , e.g. a meaningless number incremented for each row.)

The values in the table's primary key need to be unique. (Otherwise one couldn't "key" off of them, since a single key would identify multiple records.)

Just to give you a little additional information, note that a primary key is a clustered index. This means that the table is physically sorted on the disk by this index. (Thus, there can be only one clustered index per table.) So be careful with primary keys like this.

If you plan on inserting records with arbitrarily-arranged strings as a primary key, each insert will re-sort the table. As the table grows, this will impact performance. For this reason a primary key is usually simply an auto-incrementing integer field. (An integer is the native word size of the host and naturally easy to calculate and is about the "fastest" piece of data one can have.)

You can add a unique constraint to non-key columns to maintain that restriction on the data. Thus, you could have a primary key that is used primarily for data storage purposes and doesn't actually have an intuitive human-readable meaning to the data being stored. And in another column store a unique identifier that would mean something about the business data, such as an identifying string. This has the added design benefit of separating "business logic" (the identifying label) from "persistence logic" (the database's primary key).