*** Failure 3976 Incorrect referential constraint definition.
Parent Table:
============
CREATE MULTISET TABLE tutorial_db.department_hr(
dept_no INTEGER,
department_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
loc_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( dept_no );
Child Table:
============
CREATE MULTISET TABLE tutorial_db.employee_hr(
emp_no INTEGER,
emp_name VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
job_title VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
manager_id INTEGER,
hire_date DATE FORMAT 'YY/MM/DD',
salary DECIMAL(18,2),
commission DECIMAL(18,2),
dept_no INTEGER,
CONSTRAINT fkey FOREIGN KEY(dept_no) REFERENCES tutorial_db.department_hr(dept_no)
)
PRIMARY INDEX ( emp_no );
*** Failure 3977 The Parent Key is not unique or not valid.
INSERT INTO tutorial_db.employee_hr(emp_no,dept_no) VALUES(223,23);
*** Failure 2700 Referential constraint violation: invalid Foreign Key value.
Parent Table:
============
CREATE MULTISET TABLE tutorial_db.department_sr(
dept_no INTEGER,
department_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
loc_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( dept_no );
Child Table:
============
CREATE MULTISET TABLE tutorial_db.employee_sr(
emp_no INTEGER,
emp_name VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
job_title VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
manager_id INTEGER,
hire_date DATE FORMAT 'YY/MM/DD',
salary DECIMAL(18,2),
commission DECIMAL(18,2),
dept_no INTEGER REFERENCES WITH NO CHECK OPTION tutorial_db.department_sr(dept_no)
)
PRIMARY INDEX ( emp_no );
INSERT INTO tutorial_db.department_sr valuES(100, 'ACCOUNTS', 'JAIPUR');
INSERT INTO tutorial_db.department_sr valuES(200, 'R & D', 'NEW DELHI');
INSERT INTO tutorial_db.employee_sr values( 1000245, 'PRADEEP', 'PRESIDENT', null, '1981-11-17', 5000, null, 100);
INSERT INTO tutorial_db.employee_sr values( 1000258, 'BLAKE', 'SENIOR MANAGER', 1000245, '1981-05-01', 2850, null, 300);
SELECT emp_no,emp_name,salary, tutorial_db.employee_sr.dept_no, tutorial_db.department_sr.dept_no
FROM tutorial_db.employee_sr
INNER JOIN tutorial_db.department_sr ON (tutorial_db.department_sr.dept_no=tutorial_db.employee_sr.dept_no);
emp_no emp_name salary dept_no dept_no
----------- --------- --------- ----------- -----------
1000258 BLAKE 2850.00 300 300
1000245 PRADEEP 5000.00 100 100
Explain plan: Department_sr table is not referred by Teradata while fetching data.
Explanation
--------------------------------------------------------------------------------
1) First, we lock a distinct tutorial_db."pseudo table" for read on a
RowHash to prevent global deadlock for tutorial_db.employee_sr.
2) Next, we lock tutorial_db.employee_sr for read.
3) We do an all-AMPs RETRIEVE step from tutorial_db.employee_sr by
way of an all-rows scan with a condition of ("NOT
(tutorial_db.employee_sr.dept_no IS NULL)") into Spool 1
(group_amps), which is built locally on the AMPs. The size of
Spool 1 is estimated with low confidence to be 2 rows (248 bytes).
The estimated time for this step is 0.04 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.04 seconds.
|
Hard Referential Integrity |
Soft Referential Integrity |
Referenced Column |
Must be defined as Unique in parent table. |
Not necessary to be defined as Unique in parent table. |
System Overhead |
System always have to check referenced column value in parent table. |
System will not check referenced column value in parent table. No system overhead |
Onus for Maintaining Integrity |
System will maintain by itself and throw error if there is any violation. |
Teradata will assume that users will ensure integrity of data between parent & child table. |