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)),
'[email protected]',
01234567891,
'[email protected]',
99998882222);
I GET THE AFFORMENTIONED 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 COLOUMN IS NOT ALLOWED HERE.
CodePudding user response：
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 '[email protected]',
34 '01234567891',
35 '[email protected]',
36 '99998882222');
1 row created.
CodePudding user response：
You should not have a join_year column in the table as the value can be calculated from the join_date column and if it is independent then the two columns can get out-of-sync. If you did want to have it in the table then it should be a virtual column. Similarly for person_id (although if there were business reasons to persist the person_id so that it could be changed later then you could set it with a BEFORE INSERT trigger rather than using a virtual column, but either way you should not need to supply a default value derived from other columns in the INSERT statement):
CREATE TABLE personal_info (
Person_name VARCHAR(30) NOT NULL,
Date_of_Birth DATE,
Join_date DATE,
Join_year NUMBER
GENERATED ALWAYS AS (EXTRACT(YEAR FROM join_date)),
Person_address VARCHAR(75),
Person_Post VARCHAR(15),
Person_id VARCHAR(9)
GENERATED ALWAYS AS (
CAST(
TO_CHAR(EXTRACT(YEAR FROM join_date), 'FM0000')
|| TO_CHAR(empl_id, 'FM00000')
AS VARCHAR2(9)
)
)
NOT NULL
UNIQUE,
Email_primary VARCHAR(30),
Phone_primary VARCHAR2(12),
Email_secondary VARCHAR(30),
Phone_secondary VARCHAR2(12),
Sal_grade /* CHAR(1) */ NOT NULL,
Actual_Salary NUMBER(10,2),
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)
);
Note: You do not need to include the data type when a column has a foreign key constraint; if you omit it then the column will take the same data type as the primary/unique key it is referencing and you can ensure consistency between the tables.
Note 2: The EMPID_SEQ1 sequence starts with 10000 and increases by 4 to a maximum of 9999999 and the Person_id column can take 9 characters, of which 4 are the year so once there are 22500 people in the table and the sequence gets to 100000 then it will be too large for the Person_id column. You should probably either limit the sequence to 99999 or make the Person_id column larger.
Note 3: While phone numbers are numeric, they often have leading zeroes and these will not be stored in a NUMBER column so you should store phone numbers as a VARCHAR2.
Note 4: You are missing the actual_salary column.
Then you can use:
INSERT INTO PERSONAL_INFO (
Empl_id,
Person_name,
Date_of_Birth,
Join_date,
Person_address,
Sal_grade,
Actual_salary,
Person_Post,
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'),
'Banani,Dhaka.',
'D',
150000,
'SVP',
'[email protected]',
'01234567891',
'[email protected]',
'99998882222'
);