Witam,
po próbie uruchomienia fragmentu kodu SQL w Oracle SQL Developer dodającego wartości do utworzonej tabeli
otrzymuję błąd:
Error starting at line : 99 in command -
insert into Konta values('1','3490','2900','1234567890','1','1')
Error report -
SQL Error: ORA-02291: integrity constraint (ERAD.FK_ID_TRANS) violated - parent key not found
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause: A foreign key value has no matching primary key value.
*Action: Delete the foreign key or add a matching primary key.
Error starting at line : 100 in command -
insert into Konta values(Id_konta_seq_nextval,'5609034','500','2590643','2','2')
Error at Command Line : 100 Column : 26
Error report -
SQL Error: ORA-00984: column not allowed here
00984. 00000 - "column not allowed here"
*Cause:
*Action:
Error starting at line : 101 in command -
insert into Konta values(Id_konta_seq_nextval,'43574398745','3400','3642837','3','3')
Error at Command Line : 101 Column : 26
Error report -
SQL Error: ORA-00984: column not allowed here
00984. 00000 - "column not allowed here"
*Cause:
*Action:
Coś jest chyba nie tak z kluczami. Poniżej całość kodu:
DROP TABLE Klienci CASCADE CONSTRAINTS;
CREATE TABLE Klienci (
Id_klient NUMBER(5) NOT NULL,
Imie VARCHAR2(50),
Nazwisko VARCHAR2(50),
Pesel VARCHAR2(11),
CONSTRAINT Id_klient PRIMARY KEY(Id_klient)
);
DROP TABLE Konta CASCADE CONSTRAINTS;
CREATE TABLE Konta (
Id_konta NUMBER(5) NOT NULL,
Nr_rach VARCHAR2(26),
Stan_konta NUMBER(22),
Nr_karty VARCHAR2(22),
Id_klient NUMBER(5) NOT NULL,
Id_trans NUMBER(3) NOT NULL,
PRIMARY KEY(Id_konta),
--CONSTRAINT fk_Id_klient
FOREIGN KEY(Id_klient)
REFERENCES Klienci(Id_klient),
CONSTRAINT fk_Id_trans
FOREIGN KEY(Id_trans)
REFERENCES Transakcje(Id_trans)
ON DELETE cascade
);
DROP TABLE Transakcje CASCADE CONSTRAINTS;
CREATE TABLE Transakcje (
Id_trans NUMBER(5) NOT NULL,
Kwota VARCHAR2(50),
Id_odbiorca NUMBER(8) NOT NULL,
CONSTRAINT Id_trans PRIMARY KEY(Id_trans)
);
DROP TABLE Odbiorca CASCADE CONSTRAINTS;
CREATE TABLE Odbiorca (
Id_odbiorca NUMBER(8) NOT NULL,
Imie VARCHAR2(35),
Nazwisko VARCHAR2(35),
Kwota NUMBER(10),
Data_t DATE,
CONSTRAINT Id_odbiorca PRIMARY KEY(Id_odbiorca)
);
CREATE SEQUENCE Id_klienci_seq
MINVALUE 2
START WITH 2
INCREMENT BY 1
CACHE 20;
CREATE SEQUENCE Id_konta_seq
MINVALUE 2
START WITH 2
INCREMENT BY 1
CACHE 20;
CREATE SEQUENCE Id_transakcje_seq
MINVALUE 2
START WITH 2
INCREMENT BY 1
CACHE 20;
CREATE SEQUENCE Id_odbiorca_seq
MINVALUE 2
START WITH 2
INCREMENT BY 1
CACHE 20;
CREATE OR REPLACE PROCEDURE DodajKonto
(
p_nr_rach in KONTA.NR_RACH%TYPE,
p_stankonta in KONTA.STAN_KONTA%TYPE,
p_nrkarty in KONTA.NR_KARTY%TYPE,
p_idklienta in KONTA.ID_KLIENT%TYPE,
p_idtrans in KONTA.ID_TRANS%TYPE,
p_wynik OUT VARCHAR2) AS
ID number;
BEGIN
SELECT MAX(ID_KONTA) INTO ID FROM KONTA;
ID:=ID+1;
INSERT INTO KONTA(ID_KONTA, NR_RACH, STAN_KONTA, NR_KARTY, ID_KLIENT, ID_TRANS) VALUES (ID, p_idkonta, p_nr_rach, p_stankonta, p_nrkarty, p_idklienta, p_idtrans);
p_wynik:= 'Konto dodane o id= ' || ID;
commit;
EXCEPTION
WHEN OTHERS THEN p_wynik:='Wystapil blad';
END DodajKonto;
insert into Klienci values('1','Jan','Grzeb','93013066666');
insert into Klienci values(Id_klienci_seq.nextval,'Roman','Kodoń','84032078849');
insert into Klienci values(Id_klienci_seq.nextval,'Zbigniew','Niedbala','86102584402');
insert into Konta values('1','3490','2900','1234567890','1','1');
insert into Konta values(Id_konta_seq_nextval,'5609034','500','2590643','2','2');
insert into Konta values(Id_konta_seq_nextval,'43574398745','3400','3642837','3','3');
insert into Transakcje values('1','200','1');
insert into Transakcje values(Id_transakcje_seq.nextval,Id_konta_seq.nextval,'350',Id_odbiorca_seq.nextval);
insert into Transakcje values(Id_transakcje_seq.nextval,Id_konta_seq.nextval,'1200',Id_odbiorca_seq.nextval);
insert into Odbiorca values('1','Anna','Wajs','394583794','14/05/24');
insert into Odbiorca values(Id_odbiorca_seq.nextval,'Miroslaw','Kmycko','3458674794','14/06/01',Id_transakcje_seq.nextval);
insert into Odbiorca values(Id_odbiorca_seq.nextval,'Andrzej','Zales','56859044''13/01/29',Id_transakcje_seq.nextval);