db design problem...

Last Post 23 Oct 2007 11:42 PM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
chinh
New Member
New Member

--
24 Jul 2007 07:16 AM
hi,

here r 2 tables I created:

create table employee
(empl_id smallint unsigned auto_increment,
fname varchar(20),
lname carchar(20),
constraint pk_employee primary key (empl_id));

create table empl_info
(empl_id smallint unsigned,
dept varchar(20),
ext smallint unsigned,
constraint pk_empl_info primary key (empl_id,ext),
constraint fk_empl_id foreign key (empl_id) references employee (empl_id));

Why am I still allowed (no errors nor warnings) to modify (insert, update) any row in the child table even it does not exist in the parent table?
Does the foreign key suppose to constrain the values in the child table to include only values found in the parent table?
SQLUSA
New Member
New Member

--
23 Oct 2007 11:42 PM
This works in SQL Server 2005:

create table employee
(empl_id smallint identity(1,1),
fname varchar(20),
lname varchar(20),
constraint pk_employee primary key (empl_id));

create table empl_info
(empl_id smallint ,
dept varchar(20),
ext smallint ,
constraint pk_empl_info primary key (empl_id,ext),
constraint fk_empl_id foreign key (empl_id) references employee (empl_id));


insert empl_info select 5, 'pay', 2

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_empl_id". The conflict occurred in database "AdventureWorks", table "dbo.employee", column 'empl_id'.
The statement has been terminated.

Kalman Toth, Database Architect
SQL Server 2005 Training - http://www.sqlusa.com/order2005grandslam
You are not authorized to post a reply.

Acceptable Use Policy