ORA-02291:完整性约束(H00300581.FK_CLASSES)违反 - 父键没有找到
当我做插入给我这个错误:ORA-02291:完整性约束(H00300581.FK_CLASSES)违反 - 父键没有找到
ORA-02291: integrity constraint (H00300581.FK_CLASSES) violated - parent key not found
这种错误发生的所有镶入登记表。我需要帮助,请尝试为我解决。
它来自表中的外键吗?这可能是因为我尝试了一切,但它没有奏效。
创建表
drop table g3_trainers CASCADE CONSTRAINTS;
drop table g3_classes CASCADE CONSTRAINTS;
drop table g3_members CASCADE CONSTRAINTS;
drop table g3_registration CASCADE CONSTRAINTS;
CREATE TABLE G3_TRAINERS
(
TRAINER_ID VARCHAR(6) NOT NULL,
TRAINER_NAME VARCHAR(50) NOT NULL,
COUNTRY VARCHAR(25) NOT NULL,
DOB DATE NOT NULL,
SPECIALITY VARCHAR(30) NOT NULL,
DESCRIPTION VARCHAR(500) NOT NULL,
PRIMARY KEY (TRAINER_ID)
);
CREATE TABLE G3_CLASSES
(
CLASS_NUM VARCHAR(6) NOT NULL,
TRAINER_ID VARCHAR(6) NOT NULL,
CLASS_TITLE VARCHAR(50) NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE NOT NULL,
COST NUMBER(6) NOT NULL,
PRIMARY KEY (CLASS_NUM),
CONSTRAINT fk_TRAINERS FOREIGN KEY(TRAINER_ID) REFERENCES G3_TRAINERS(TRAINER_ID)
);
CREATE TABLE G3_MEMBERS
(
MEMBER_ID VARCHAR(6) NOT NULL,
MEMBER_NAME VARCHAR(50) NOT NULL,
MEMBER_AGE NUMBER(2) NOT NULL,
GENDER VARCHAR(10) NOT NULL,
PHONE_NUMBER VARCHAR(10) NOT NULL,
CHECK (regexp_like (PHONE_NUMBER,'^(\d{3}-\d{3}-?\d{4}|\d{10})$')),
CHECK (MEMBER_AGE>=17),
CHECK(MEMBER_AGE NOT LIKE '%[^a-zA-Z]%'),
PRIMARY KEY (MEMBER_ID)
);
CREATE TABLE G3_REGISTRATION
(
REGISTRATION_ID VARCHAR(6) NOT NULL,
MEMBER_ID VARCHAR(6) NOT NULL,
AMOUNT_TO_PAY NUMBER(6) NOT NULL,
CLASS_NUM VARCHAR(6) NOT NULL,
PRIMARY KEY (REGISTRATION_ID),
CONSTRAINT fk_MEMBERS FOREIGN KEY(MEMBER_ID) REFERENCES G3_MEMBERS(MEMBER_ID),
CONSTRAINT fk_CLASSES FOREIGN KEY(CLASS_NUM) REFERENCES G3_CLASSES(CLASS_NUM)
);
创建序列
CREATE SEQUENCE G3_TRAINER_Seq
START WITH 10001
INCREMENT BY 1
NOCACHE
NOCYCLE;
CREATE SEQUENCE G3_MEMBER_Seq
START WITH 20001
INCREMENT BY 1
NOCACHE
NOCYCLE;
CREATE SEQUENCE G3_CLASSE_Seq
START WITH 30001
INCREMENT BY 1
NOCACHE
NOCYCLE;
CREATE SEQUENCE G3_REGISTRATIONS_Seq
START WITH 40001
INCREMENT BY 1
NOCACHE
NOCYCLE;
训练数据
INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION)
VALUES (G3_TRAINER_Seq.nextVal, 'Ahmed', 'Eygpt', '2/3/1988', 'Body Building', 'I describe my self as an active guy i workout everyday and thats why i desided to train people to be good as me');
INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION)
VALUES (G3_TRAINER_Seq.nextVal, 'John', 'Canada', '4/6/1987', 'Crossfit', 'I like being healthy and work as much as i can to be fit and to have a great life');
INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION)
VALUES (G3_TRAINER_Seq.nextVal, 'Sara', 'Canada', '9/1/1990', 'Yoga', 'I like interacting with poeple, and to teach them how yoga will be a very good source of flexing and relaxing your body');
INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION)
VALUES (G3_TRAINER_Seq.nextVal, 'Jun', 'China', '12/4/1981', 'Crossfit', 'I describe my self as an daily working guy i like my job because i get to know more about people and i teach many people how to be fit');
INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION)
VALUES (G3_TRAINER_Seq.nextVal, 'Mike', 'Ohio', '2/22/1981', 'Body Building', 'Being strong and big is what i am aiming for, i like keeping my diet healthy and to train poeple to be healthy and big like me');
INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION)
VALUES (G3_TRAINER_Seq.nextVal, 'Mia', 'Germany', '12/12/1987', 'Yoga', 'I describe myself as a hard working i love my job and i love my members');
INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION)
VALUES (G3_TRAINER_Seq.nextVal, 'Essam', 'UAE', '1/5/1980', 'Body Building', 'I am a hard worker and i like to train my members and i want them to be healthy and strong');
类的数据
INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST)
VALUES (G3_CLASSE_Seq.nextVal, 10001, 'Weight Lifting Class', '1/12/2017', '1/1/2018', 1150);
INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST)
VALUES (G3_CLASSE_Seq.nextVal, 10001, 'Weight Lifting Class', '1/2/2018', '1/3/2018', 1150);
INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST)
VALUES (G3_CLASSE_Seq.nextVal, 10002, 'Body Weight Class', '2/1/2018', '2/2/2018', 950);
INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST)
VALUES (G3_CLASSE_Seq.nextVal, 10003, 'Yoga Class', '3/2/2018', '3/3/2018', 750);
INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST)
VALUES (G3_CLASSE_Seq.nextVal, 10003, 'Yoga Class', '5/5/2018', '5/6/2018', 750);
INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST)
VALUES (G3_CLASSE_Seq.nextVal, 10004, 'Body Weight Class', '10/2/2018', '10/3/2018', 950);
INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST)
VALUES (G3_CLASSE_Seq.nextVal, 10004, 'Body Weight Class', '10/4/2018', '10/5/2018', 950);
INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST)
VALUES (G3_CLASSE_Seq.nextVal, 10005, 'Weight Lifting Class', '12/20/2017', '1/20/2018', 1150);
INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST)
VALUES (G3_CLASSE_Seq.nextVal, 10005, 'Weight Lifting Class', '2/20/2018', '3/20/2018', 1150);
INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST)
VALUES (G3_CLASSE_Seq.nextVal, 10005, 'Weight Lifting Class', '4/20/2018', '5/20/2018', 1150);
INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST)
VALUES (G3_CLASSE_Seq.nextVal, 10006, 'Yoga Class', '12/28/2017', '1/28/2018', 750);
INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST)
VALUES (G3_CLASSE_Seq.nextVal, 10007, 'Weight Lifting Class', '10/3/2018', '10/4/2018', 1150);
INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST)
VALUES (G3_CLASSE_Seq.nextVal, 10007, 'Weight Lifting Class', '10/5/2018', '10/6/2018', 1150);
个
成员数据
INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER)
VALUES (G3_MEMBER_Seq.nextVal, 'Mohammed', '21', 'Male', '0502202212');
INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER)
VALUES (G3_MEMBER_Seq.nextVal, 'Afra', '28', 'Female', '0555544558');
INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER)
VALUES (G3_MEMBER_Seq.nextVal, 'Essa', '30', 'Male', '0555544558');
INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER)
VALUES (G3_MEMBER_Seq.nextVal, 'Ahmed', '19', 'Male', '0563020300');
INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER)
VALUES (G3_MEMBER_Seq.nextVal, 'Alex', '25', 'Female', '0521022932');
INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER)
VALUES (G3_MEMBER_Seq.nextVal, 'Abdulla', '24', 'Male', '0504493393');
INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER)
VALUES (G3_MEMBER_Seq.nextVal, 'Abdulrahman', '20', 'Male', '0502292290');
INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER)
VALUES (G3_MEMBER_Seq.nextVal, 'Olivia', '21', 'Female', '0561020200');
INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER)
VALUES (G3_MEMBER_Seq.nextVal, 'Omar', '30', 'Male', '0502503323');
INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER)
VALUES (G3_MEMBER_Seq.nextVal, 'Nasser', '29', 'Male', '0504334439');
INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER)
VALUES (G3_MEMBER_Seq.nextVal, 'Sophia', '22', 'Female', '0561616316');
INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER)
VALUES (G3_MEMBER_Seq.nextVal, 'Nadia', '21', 'Female', '0507797786');
注册数据
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20001, 1150, 30001);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20001, 750, 30004);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20002, 750, 30004);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20002, 950, 30006);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20003, 1150, 30001);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20003, 1150, 300010);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20004, 950, 30006);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20004, 1150, 30010);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20005, 1150, 30007);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20005, 750, 30011);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20006, 1150, 30013);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20006, 1150, 30012);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20007, 1150, 30009);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20007, 750, 30011);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20008, 1150, 30008);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20008, 750, 30011);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20009, 1150, 30013);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20009, 750, 30005);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20010, 1150, 30013);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20010, 1150, 30012);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20010, 950, 30007);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20011, 1150, 30013);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20011, 750, 30005);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20012, 750, 30004);
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM)
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20012, 1150, 30009);
commas
缺少的大多数语句,如:
between
'Eygpt'
&'12/3/1988'
in :INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) VALUES (G3_TRAINERS_Seq.nextVal, 'Ahmed', 'Eygpt' '12/3/1988', 'Body Building', 'I describe my self as an active guy i workout everyday and thats why i desided to train people to be good as me');
OR
between
'Weight Lifting Class'
&'1/12/2017'
INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) VALUES (G3_CLASSES_Seq.nextVal, 10001, 'Weight Lifting Class' '1/12/2017', '1/1/2018', 1150);
你有CONSTRAINT fk_CLASSES FOREIGN KEY(CLASS_NUM) REFERENCES G3_CLASSES(CLASS_NUM)
为约束
你G3_CLASSES_Seq
序列填充g3_classes
表的class_num列,应该是方便,fk_CLASSES FOREIGN KEY
,但你有另一个序列G3_CLASSE_Seq
开始30000
。顺便说一句,现在看来,这应该与30001
i.e. Pay attention that
G3_CLASSE_Seq
andG3_CLASSES_Seq
are different.
的方式启动,删除空格在column names
之间create sentences
类似以下内容:
为例,其转换
CREATE TABLE G3_TRAINERS
(
TRAINER_ID VARCHAR(6) NOT NULL,
TRAINER_NAME VARCHAR(50) NOT NULL,
COUNTRY VARCHAR(25) NOT NULL,
DOB DATE NOT NULL,
SPECIALITY VARCHAR(30) NOT NULL,
DESCRIPTION VARCHAR(500) NOT NULL,
PRIMARY KEY (TRAINER_ID)
);
对此:
CREATE TABLE G3_TRAINERS
(
TRAINER_ID VARCHAR(6) NOT NULL,
TRAINER_NAME VARCHAR(50) NOT NULL,
COUNTRY VARCHAR(25) NOT NULL,
DOB DATE NOT NULL,
SPECIALITY VARCHAR(30) NOT NULL,
DESCRIPTION VARCHAR(500) NOT NULL,
PRIMARY KEY (TRAINER_ID)
);
An d在以下语句中存在另一个问题(300010
)
INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) VALUES (G3_REGISTRATIONS_Seq.nextVal, 20003, 1150,
300010
);
非常感谢,解决了我的其他问题之一,但我发布了主要问题 –
@abdulrahmanarab请看我最后编辑的外键和序列。 –
@abdulrahmanarab我认为,问题不是使用'G3_CLASSE_Seq'(你应该使用这个,从30001开始)。您正在使用'G3_CLASSES_Seq'。 –
您的脚本有太多错误。有几个地方丢失了逗号。您在插入语句中使用的序列名称中存在拼写错误。首先解决这些问题。 –
@Kaushik Nayak我修复了一些小问题,我如何添加代码? –
@Kaushik Nayak我编辑它看看它 –