How can i solve the unique constraint error in sql program?-CodePudding

I have homework to create tables and insert data into it and when i insert data into Branch table it gives me a unique constraint error and I don’t know why, and how can I solve this problem. my codes:

create table Branch( 
Branchno varchar (4) primary key not null, 
Street varchar (15) not null, 
City varchar (10) not null, 
Postcode varchar (10) not null)



create table staff (
Staffno       varchar (4),
Fname         varchar2 (50) not null,
Lname         varchar (50),
Position      varchar2 (50),
Sex           varchar (1),
Dob           date,
Salary        number (30),
Branchno      varchar (4),
constraint pk_staff primary key (staffno),
constraint fk_staff_branchno foreign key (branchno)
     references Branch (branchno)
)




create table Client 
(Clientno varchar (4) primary key not null, 
Fname varchar (50) not null,
Lname varchar (50) not null,
Telno varchar (50) not null,
Preftype varchar (18) not null,
Maxrent INT)






create table PropertyForRent(
Propertyno       varchar (40) not null,
Street           varchar (40),
City             varchar (40),
Postcode         varchar (40),
Type             varchar (40),
Rooms            number (30),
Rent             number (30),
Ownerno          varchar (40),
Staffno          varchar (40),
Branchno         varchar (40),
constraint pk_PropertyForRent primary key (Propertyno),
foreign key (staffno) 
     references staff (staffno),
foreign key (branchno)
     references branch (branchno)
)








create table Registration
(clientno       varchar (60) not null,
branchno        varchar (50) not null,
staffno         varchar (50) not null,
datejoined      date         not null,
foreign key (clientno) references client (clientno),
foreign key (branchno) references branch (branchno),
foreign key (staffno) references staff (staffno)
)

The data that I insert to give me this error:

INSERT INTO Branch values
('B005','22 Deer Rd','London','SW1 4EH'); 
INSERT INTO Branch values
('B007','16 Argy11 St','Aberdeen','AB2 3SU');

CodePudding user response：

I try with Oracle 11g and not receive any error:

SQL> INSERT INTO Branch values ('B005','22 Deer Rd','London','SW1 4EH'); 2

1 row created.

SQL> INSERT INTO Branch values ('B007','16 Argy11 St','Aberdeen','AB2 3SU'); 2

1 row created.

SQL> commit;

Commit complete.

Probably have you already record with these keys or have you any trigger on this table.

CodePudding user response：

Instead of an INSERT INTO

you could

MERGE INTO Branch dest
  USING( SELECT 'B005' Branchno,'22 Deer Rd' Street,'London' City,'SW1 4EH' Postcode FROM dual) src
ON( dest.Branchno = src.Branchno )
 WHEN NOT MATCHED THEN
   INSERT( Branchno, Street, City,Postcode ) 
     VALUES ( src.Branchno, src.Street, src.City,src.Postcode );

That looks on the first sight, more difficult, but you would not get an error and all stops.

trggers can't stop you from inserting doulbe entires as they you can only can raise errors with wuld stop again every multi insert

see example