這是我創建的表：
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)
);
這是員工表：
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;
我已經插入員工表，沒有問題。
INSERT INTO employee (Employee_id, Employee_job_description, Proj_id, Dep_id) VALUES(EMPID_SEQ1.NEXTVAL,'SENIOR VICE PRESIDENT',501,1);
但是當我嘗試插入 PERSONAL_INFO 表時：
/* 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);
我在插入 PERSON_ID 唯一鍵值時收到 AFFORMENTIONED 錯誤。基本上，我希望 PERSON_ID 看起來像“200710016”。員工 ID 之后的入職年份。但它告訴我，這里不允許使用 JOIN_YEAR 列。
uj5u.com熱心網友回復：
您不能參考剛剛插入的列 - 您必須再次“重復”相同的資料。
此外，應該修改目標表（兩種資料型別——電話號碼不是真正的“數字”，因為可能有前導零；缺少一列）。
從序列開始，您首先必須選擇nextval，然后currval因為 - 最初 -currval不存在。
固定時：
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.
插入：
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.
uj5u.com熱心網友回復：
您不應該join_year在表中有列，因為可以從join_date列中計算出值，如果它是獨立的，那么兩列可能會不同步。如果您確實想將它放在表中，那么它應該是一個虛擬列。類似地person_id（盡管如果有業務原因要持久化person_id它以便以后可以更改，那么您可以使用BEFORE INSERT觸發器而不是使用虛擬列來設定它，但無論哪種方式，您都不需要提供從其他列派生的默認值在INSERT宣告中）：
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)
);
注意：當列有外鍵約束時，不需要包含資料型別；如果您省略它，那么該列將采用與它所參考的主鍵/唯一鍵相同的資料型別，并且您可以確保表之間的一致性。
注 2：EMPID_SEQ1序列以 10000 開頭，增加 4 到最大 9999999，該Person_id列可以占用 9 個字符，其中 4 個是年份，所以一旦表中有 22500 人并且序列達到 100000，那么它將是列太大Person_id。您可能應該將序列限制為 99999 或使Person_id列更大。
注意 3：雖然電話號碼是數字，但它們通常有前導零，這些不會存盤在NUMBER列中，因此您應該將電話號碼存盤為VARCHAR2.
注意 4：您缺少該actual_salary列。
然后你可以使用：
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'
);
小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/518040.html