| View previous topic :: View next topic |
| Author |
Message |
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
 Posted: Tue Jan 10, 2006 6:12 pm Post subject: Referential Constraint |
 |
|
I've looked the doc and still I still don't understand. Here is what I need:
| Code: |
Table 1 contains Departments
Primary key is Departments
Table 2 contains Employee and Dept
Primary key is Employee
Foreign key is Dept
References Table1 (Departments)
on Delete ?????
|
My requirements are:
Table1 will not allow a delete if a row exist in Table 2.
Table2 will not allow an Insert/Update if not in Table1.
Table2 can Delete a row.
This is almost a copy of the IBM doc, I don't understand the 'On Delete' references.
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Wed Jan 11, 2006 11:05 am Post subject: |
|
|
So here is how I think it should be coded.
| Code: |
ALL TABLESPACES HAVE BEEN CREATED.
CREATE TABLE FDBMD.TABLE1
(DEPARTMENT CHAR(25) NOT NULL,
PRIMARY KEY (DEPARTMENT))
IN FDBMD.FTSCLMM1
;
CREATE UNIQUE INDEX FDBMD.XTABLE1
ON FDBMD.TABLE1
(DEPARTMENT)
USING STOGROUP FSGALLT
PRIQTY 450
SECQTY 225
BUFFERPOOL BP11
CLOSE NO;
CREATE TABLE FDBMD.TABLE2
(EMPLOYEE INTERGER NOT NULL,
DEPT CHAR(25) NOT NULL,
PRIMARY KEY (EMPLOYEE)
FOREIGN KEY (DEPT)
REFERENCES TABLE1 (DEPARTMENT)
ON DELETE RESTRICT
IN FDBMD.FTSCLMM2
;
CREATE UNIQUE INDEX FDBMD.XTABLE2
ON FDBMD.TABLE1
(EMPLOYEE)
USING STOGROUP FSGALLT
PRIQTY 450
SECQTY 225
BUFFERPOOL BP11
CLOSE NO;
ALTER TABLE TABLE1
FOREIGN KEY (DEPARTMENT)
REFERENCES TABLE2 (DEPT)
ON DELETE RESTRICT;
|
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
