HERE'S THE TABLE I CREATED:
CREATE TABLE personal_info (
Person_name VARCHAR(30) NOT NULL,
Date_of_Birth DATE,
Join_date DATE,
Join_year NUMBER,
Person_address VARCHAR(75),
Person_Post VARCHAR(15),
Person_id VARCHAR(9) NOT NULL UNIQUE,
Email_primary VARCHAR(30),
Phone_primary NUMBER,
Email_secondary VARCHAR(30),
Phone_secondary NUMBER,
Sal_grade CHAR(1) NOT NULL,
Empl_id NUMBER NOT NULL,
CONSTRAINT FK_Salary_Person FOREIGN KEY (Sal_grade) REFERENCES salary(Salary_grade) ON DELETE CASCADE,
CONSTRAINT FK_Employee_Person FOREIGN KEY (Empl_id) REFERENCES employee(Employee_id) ON DELETE CASCADE,
CONSTRAINT UC_Person_ID UNIQUE (Empl_id,Person_name)
);
HERE'S THE EMPLOYEE TABLE:
CREATE TABLE employee (
Employee_id NUMBER NOT NULL PRIMARY KEY,
Employee_job_description VARCHAR(200),
Proj_id NUMBER NOT NULL,
Dep_id NUMBER NOT NULL
);
ALTER TABLE employee
ADD CONSTRAINT FK_project_employee
FOREIGN KEY (Proj_id) REFERENCES PROJECTS(Project_id) ON DELETE CASCADE;
ALTER TABLE employee
ADD CONSTRAINT FK_dept_employee
FOREIGN KEY (Dep_id) REFERENCES dept(Dept_id) ON DELETE CASCADE;
CREATE SEQUENCE EMPID_SEQ1
MINVALUE 1
MAXVALUE 9999999
START WITH 10000
INCREMENT BY 4
CACHE 20;
I ALREADY INSERTED INTO THE EMPLOYEE TABLE, NO ISSUE.
INSERT INTO employee (Employee_id, Employee_job_description, Proj_id, Dep_id) VALUES(EMPID_SEQ1.NEXTVAL,'SENIOR VICE PRESIDENT',501,1);
BUT WHEN I TRY TO INSERT INTO THE PERSONAL_INFO TABLE:
/* Formatted on 19-Oct-22 11:58:19 AM (QP5 v5.256.13226.35538) */
INSERT INTO PERSONAL_INFO (Empl_id,
Person_name,
Date_of_Birth,
Join_date,
Join_year,
Person_address,
Sal_grade,
Actual_salary,
Person_Post,
PERSON_ID,
Email_primary,
Phone_primary,
Email_secondary,
Phone_secondary)
VALUES (EMPID_SEQ1.CURRVAL,
'Mr. FF',
TO_DATE ('1980/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'),
TO_DATE ('2000/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'),
TO_CHAR (Join_DATE, 'YYYY'),
'Banani,Dhaka.',
'D',
150000,
'SVP',
TO_CHAR(TO_CHAR(Join_YEAR) || TO_CHAR (EMPID_SEQ1.CURRVAL)),
'FF@bank.com',
01234567891,
'FFF@bank.com',
99998882222);
I GET THE AFORMENTIONED ERROR WHILE INSERTING THE PERSON_ID UNIQUE KEY VALUE. BASICALLY I WANTED THE PERSON_ID TO LOOK SOMETHING LIKE '200710016'. JOINING YEAR FOLLOWED BY EMPLOYEE ID.
BUT IT'S TELLING ME THAT THE JOIN_YEAR COLUMN IS NOT ALLOWED HERE.
You can't reference a column that's just being inserted - you'll have to "repeat" the same data again.
Also, target table should be modified (two datatypes - phone numbers aren't really "numbers" because of possible leading zeros; one column is missing).
As of the sequence, you first have to select nextval and then currval because - initially - currval doesn't exist.
When fixed:
SQL> CREATE TABLE personal_info
2 (
3 Person_name VARCHAR (30) NOT NULL,
4 Date_of_Birth DATE,
5 Join_date DATE,
6 Join_year NUMBER,
7 Person_address VARCHAR (75),
8 Person_Post VARCHAR (15),
9 Person_id VARCHAR (9) NOT NULL UNIQUE,
10 Email_primary VARCHAR (30),
11 Phone_primary VARCHAR (30), --> change datatype
12 Email_secondary VARCHAR (30),
13 Phone_secondary VARCHAR (30), --> change datatype
14 Sal_grade CHAR (1) NOT NULL,
15 Empl_id NUMBER NOT NULL,
16 actual_salary NUMBER --> add this column
17 );
Table created.
Insert:
SQL> INSERT INTO PERSONAL_INFO (Empl_id,
2 Person_name,
3 Date_of_Birth,
4 Join_date,
5 Join_year,
6 Person_address,
7 Sal_grade,
8 Actual_salary,
9 Person_Post,
10 PERSON_ID,
11 Email_primary,
12 Phone_primary,
13 Email_secondary,
14 Phone_secondary)
15 VALUES (
16 EMPID_SEQ1.nextval,
17 'Mr. FF',
18 TO_DATE ('1980/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'),
19 TO_DATE ('2000/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'),
20 TO_CHAR (
21 TO_DATE ('2000/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'),
22 'YYYY'),
23 'Banani,Dhaka.',
24 'D',
25 150000,
26 'SVP',
27 TO_CHAR (
28 TO_CHAR (
29 TO_DATE ('2000/05/03 21:02:44',
30 'yyyy/mm/dd hh24:mi:ss'),
31 'YYYY')
32 || TO_CHAR (EMPID_SEQ1.CURRVAL)),
33 'FF@bank.com',
34 '01234567891',
35 'FFF@bank.com',
36 '99998882222');
1 row created.