How to insert data into SQL table?

Question

I understand the basics of SQL and databases. However every time I run my script to insert data into a table, I get the following error:

ORA-00001: unique constraint (string.string) violated tips

This is my code:

DROP TABLE Bag;

CREATE TABLE Bag
(
    BranchID varchar2(3),
    HouseName varchar2(16),
    StreetName varchar2(16),
    City varchar2(16),
    Postcode varchar2(6),
    TelephoneNo1 number,
    TelephoneNo2 number,

    PRIMARY KEY(BranchID)
);

INSERT INTO Bag 
VALUES (01, 'Hayway', 'Jay Rd', 'Newcastle', 'N9R5DT', 09088, 09077);

There are several issues with the code. But the first insert on an empty table should not be causing this error. — Gordon Linoff
– Gordon Linoff, Commented May 10, 2017 at 18:20
For once, you create the column BranchID as a varchar2(3), but you attempt to insert a number into it... — Jakob Busk Sørensen
– Jakob Busk Sørensen, Commented May 10, 2017 at 18:22
It is, but generally you want to use an integer type for integers. — Jacob H
– Jacob H, Commented May 10, 2017 at 18:24

War · Accepted Answer · 2017-05-10 19:39:05Z

2

As per the documentation the correct syntax should be ...

INSERT INTO Bag 
   (BranchID, HouseName, StreetName, City, Postcode, TelephoneNo1, TelephoneNo2)
VALUES 
   ('01', 'Hayway', 'Jay Rd', 'Newcastle', 'N9R5DT', 09088, 09077);

Reference:

https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems025.htm https://www.techonthenet.com/oracle/insert.php (easier to read)

answered May 10, 2017 at 19:39

War

8,6584 gold badges54 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gordon Linoff Over a year ago

(1) The column references are highly desirable but not required. (2) This would have nothing to do with the unique constraint. So although I understand the answer and upvotes, I have no idea why the OP accepted it.

War Over a year ago

My understanding was that the issue was caused by the execution engine not knowing what of the values was mapped to the PK and adding the column names fixes it thus confirming my theory.

War Over a year ago

Interesting ... maybe he spec or implementation in oracle is wrong then ... can't say i've ever seen a working example of an insert that doesn't provide the column info in one or another. I'd be curious to see an example of this Gordon?

War Over a year ago

Also if it wasn't that then what was the issue?

Collectives™ on Stack Overflow

How to insert data into SQL table?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related