reference constraint not working?

[rommel]

Hi,

I've create 2 tables in my NT Oracle 8.1.7 DB as follows...

Create table Dept (deptno varchar2(10) primary key,
deptname varchar2(30));

Create table Emp (empname varchar2(30), deptno varchar2(10) references Dept) ;


The Emp table is the child of Dept and it references the column deptno.

My question is, why is that I can insert data into Emp without
any match in my Dept table? May Dept table is empty yet.

Isn't it that "references" contraints is designed to do this?


Thanks in advance,

[SANJAY_G]

It shouldn't allow to do that, check whether the constaint is enabled?
Select constraint_name, status from user_constraints;

Sanjay

[rommel]

Hi Sanjay,

I run the command and it listed all my constraints as ENABLED.

Any other suggestions?


Thanks

[SANJAY_G]

I tried the same thing with dept table empty and got the following error..

SQL> insert into emp values ('Sanjay', 10);
insert into emp values ('Sanjay', 10)
ERROR at line 1:
ORA-02291: integrity constraint (SANJAY.SYS_C001242) violated - parent key not found

I don't see any other reason...

Sanjay

[rommel]

Hi,

I really don't know why....

[pipo]

you don't use the correct syntax for creating your reference key, try that :

Create table Emp
(empname varchar2(30),
deptno varchar2(10) constraint fk_dept
references Dept(deptno)) ;

[rommel]

Hi Sanjay, Pipo,


Actually Emp and Dept are just my sample, so I won't type
the actual tables w/c are quite long.

I also try the sample and it worked. Kindly Help me find the
error with this one pls.......

create table clients
(company_name varchar2(40) not null,
company_id varchar2(5) primary key,
status varchar2(15) not null,
Tel varchar2(10),
FAX varchar2(10),
segment_type varchar2(15),
email varchar2(30));

create index nx11 on clients (company_name);

create table contact_persons
(company_id varchar2(5) references clients,
first_name varchar2(15),
mid_name varchar2(15),
last_name varchar2(15),
position varchar2(15),
department varchar2(15));

create index nx1 on contact_person (company_id);


Please try it in your DB and insert data in contact_persons

Thanks again,

[pipo]

why don't you read the answer I provide you ???
you do NOT use the correct syntax, you need to specify which column is referenced, not just the table