473,323 Members | 1,574 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

integrity constraints problem

I am trying to create my tables where if i delete/update a record from one table, all the other tables are affected by deleting/updating any records that reference the original record.

For example, if i delete/update a record from the employee table, the other tables that are referenced to it will be updated/deleted

Here is the database create SQL i have

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE Employee (
  2.   ssn             NUMBER (4) PRIMARY KEY,
  3.   salary          NUMBER (5),
  4.   phone          NUMBER (11)
  5.   );
  6.  
  7. CREATE TABLE Department (
  8.   dno          NUMBER (1) PRIMARY KEY,
  9.   dname          VARCHAR2 (20),
  10.   budget      NUMBER (6)
  11.   );
  12.  
  13. CREATE TABLE Child (
  14.   name            VARCHAR2 (20) UNIQUE,
  15.   age          NUMBER (2),
  16.   check(age BETWEEN 0 AND 18)
  17.   );    
  18.  
  19. CREATE TABLE Children (
  20.   ssn          NUMBER (4),
  21.   name          VARCHAR2 (20),
  22.   CONSTRAINT emp_FK FOREIGN KEY(ssn) references Employee(ssn) ON DELETE SET NULL ON UPDATE CASCADE,
  23.   CONSTRAINT chd_FK FOREIGN KEY(name) references Child(name) ON DELETE SET NULL ON UPDATE CASCADE
  24.   );
  25.  
  26. CREATE TABLE Works (
  27.   ssn          NUMBER (4),
  28.   dno          NUMBER (1),
  29.   CONSTRAINT empl_FK FOREIGN KEY(ssn) references Employee(ssn) ON DELETE SET NULL ON UPDATE CASCADE, 
  30.   CONSTRAINT dept_FK FOREIGN KEY(dno) references Department(dno) ON DELETE SET NULL ON UPDATE CASCADE 
  31.   );
  32.  
  33. CREATE TABLE Manages (
  34.   ssn          NUMBER (4),
  35.   dno          NUMBER (1),
  36.   CONSTRAINT employ_FK FOREIGN KEY(ssn) references Employee(ssn) ON DELETE SET NULL ON UPDATE CASCADE, 
  37.   CONSTRAINT depart_FK FOREIGN KEY(dno) references Department(dno) ON DELETE SET NULL ON UPDATE CASCADE 
  38.   );
  39.  
  40. CREATE TABLE NewMember (
  41.   ssn          NUMBER (4),
  42.   dno           NUMBER (1)
  43.   );
When i try to run the code I get this error

ORA-00907: missing right parenthesis
and it points to these lines

Expand|Select|Wrap|Line Numbers
  1. CONSTRAINT emp_FK FOREIGN KEY(ssn) references Employee(ssn) ON DELETE SET NULL ON UPDATE CASCADE,
  2. CONSTRAINT empl_FK FOREIGN KEY(ssn) references Employee(ssn) ON DELETE SET NULL ON UPDATE CASCADE
  3. CONSTRAINT employ_FK FOREIGN KEY(ssn) references Employee(ssn) ON DELETE SET NULL ON UPDATE CASCADE
It seems strange that the other foreign keys aren't affected and i don't know if they will when i fix this problem
Nov 24 '07 #1
4 3957
amitpatel66
2,367 Expert 2GB
Try this:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE Children (
  2.   ssn          NUMBER (4),
  3.   name          VARCHAR2 (20),
  4.   CONSTRAINT emp_FK FOREIGN KEY(ssn) references Employee(ssn) ON DELETE SET NULL,
  5.   CONSTRAINT chd_FK FOREIGN KEY(name) references Child(name) ON DELETE SET NULL 
  6.   );
  7.  
Try the same for all other CREATE table statements as well
Nov 25 '07 #2
I figured it out last night. It turns out that Oracle doesn't have an ON UPDATE constraint so i deleted that and it worked
Nov 25 '07 #3
amitpatel66
2,367 Expert 2GB
I figured it out last night. It turns out that Oracle doesn't have an ON UPDATE constraint so i deleted that and it worked
Yes thats right. but you can achieve this by creating user defined procedures and a trigger to achieve this functionality!!
Nov 26 '07 #4
rallen
1
Yes thats right. but you can achieve this by creating user defined procedures and a trigger to achieve this functionality!!

You can also use dbConstructor to create your scripts and it will automatically generate the declarative and trigger referential constrain.

Regards,

Robert Allen Schambach
Nov 28 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: obhayes | last post by:
Hi, I have two tables Table A and B, below with some dummy data... Table A (contains specific unique settings that can be requested) Id, SettingName 1, weight 2, length Table B (contains...
4
by: maricel | last post by:
I have the following base table structure - DDL: CREATE TABLE "ADMINISTRATOR"."T1" ( "C1" INTEGER NOT NULL ) IN "TEST_TS" ; ALTER TABLE "ADMINISTRATOR"."T1" ADD PRIMARY KEY
6
by: Christian Rank | last post by:
Hello, I came across the following problem with integrity constraints and PL/pgSQL (PostgreSQL version used: 7.4.2): I defined the following tables, constraints and data: create table a (n...
1
by: Grant McLean | last post by:
Hi First a simple question ... I have a table "access_log" that has foreign keys "app_id" and "app_user_id" that reference the "application_type" and "app_user" tables. When I insert into...
6
by: Greg Stark | last post by:
There's another poster complaining about referential integrity checks causing deadlocks. Unfortunately I've deleted the message so this response (and the archives aren't responding) isn't going to...
7
by: Jimmie H. Apsey | last post by:
Referential Integrity on one of our production tables seems to have been lost. I am running Postgres 7.1.3 embedded within Red Hat kernel-2.4.9-e.49. Within that I have a table with referential...
6
by: Jeff North | last post by:
I'm using Microsoft SQL Server Management Studio Express 9.00.2047.00 and expriencing problems with setting referential integrity on a link table. The tables' schema is as follows:...
80
by: Andrew R | last post by:
Hi I'm creating a series of forms, each with with around 15-20 text boxes. The text boxes will show data from tables, but are unbound to make them more flexible. I want the form to be used...
3
by: shsandeep | last post by:
In a data warehousing application, what is the impact of imposing referential integrity on the database side? Does it help or degrade the performance considering the complex transformations that...
2
by: njames | last post by:
There are two doubts regarding integrity constraints : 1) Can the constraints "NOT NULL" and "DEFAULT" be assigned at table level ? i have tried using the syntax of CHECK constraint at table...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.