September 22, 2010 at 3:55 pm
Here is my dilemma. I have 2 tables 1 parent and 1 child. I established a relationship between the two tables based upon the primary key of the parent and the foreign key of the child. The problem is when I open the child table I am unable to enter a value for the foreign key. Is this standard behavior. Is there a problem with a column constrain. Thanks In advance for any help or assistance you can provide.
Another issue I was wondering if someone could give me some advice concerning. How would I construct a Stored procedure that can check the Primary Key Value of the Parent Table to see if it contains a value a user has specified in a parameter value in the foreign key column of a child table. If the value in the primary key column of the parent table = the value in the foreign key column of the child table then I want to insert a child record. I have seen a few examples that utilize the IF EXISTS KEY WORD.
September 22, 2010 at 4:48 pm
ronaldkrex (9/22/2010)
Here is my dilemma. I have 2 tables 1 parent and 1 child. I established a relationship between the two tables based upon the primary key of the parent and the foreign key of the child. The problem is when I open the child table I am unable to enter a value for the foreign key. Is this standard behavior. Is there a problem with a column constrain.
That's the beauty of referential integrity, RDBMS would not allow to insert a row in Child table if there is no parent row in Parent table.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 22, 2010 at 5:03 pm
create table parents (id int not null primary key, name varchar(40))
go
create table child (parent_id int not null constraint FK_childs_parent foreign key references dbo.parents(id)
, name varchar(40) not null
, age tinyint null
, constraint pk_child primary key (parent_id, name)
)
go
--drop proc add_child
go
create proc add_child @parent int, @child varchar(40), @age tinyint
as
-- code example
--set nocount on
if not exists (select 1 from dbo.parents where id = @parent) begin
raiserror('Unknown parent %d for child "%s".', 10, 1, @parent, @child)
return -10
end
insert child (parent_id, name, age) values (@parent, @child, @age)
return @@error
go
exec dbo.add_child 1, 'me', 86
You should see:
Unknown parent 1 for child "me".
September 22, 2010 at 11:55 pm
When you are enforcing a relationship you should have the data on the parent table without that you will not be able to enter data into child table.
for ex. parent table has employeeid as primay key and this is foreign key for your child table.
you have employeeid in your parent table (1,2,3)
if you want to enter 4 in child table it will not allow you to insert any record on child table. you need enter 4 into parent table first and then you have insert the related records into child table.
Regards,
Subbu
http://www.mssqlforum.wordpress.com
Regards,
Subbu
Click here to Get Speedy answer or solution
September 23, 2010 at 12:30 am
Thanks this is helpful
September 23, 2010 at 4:30 am
Thanks for your Help. I was wondering since I already have my parent and child table. Could I just use this part of the SQL you posted? Also why is the last line exec dbo.add_child 1, 'me', 86 needed? Thanks Again.
if not exists (select 1 from dbo.parents where id = @parent) begin
raiserror('Unknown parent %d for child "%s".', 10, 1, @parent, @child)
return -10
end
insert child (parent_id, name, age) values (@parent, @child, @age)
return @@error
go
exec dbo.add_child 1, 'me', 86
September 23, 2010 at 4:34 am
Thanks for your Help. But what if I have a value in the parent Table Primary Key column. And I am trying to enter that Same value in the child tables foreign key column, but it want let me enter anything? Do you think I might have a setting on that needs to be changed? Thanks Again.
September 23, 2010 at 5:42 am
ronaldkrex (9/23/2010)
Thanks for your Help. But what if I have a value in the parent Table Primary Key column. And I am trying to enter that Same value in the child tables foreign key column, but it want let me enter anything?
Show us the scripts, copy/paste whole session showing the issue.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 23, 2010 at 9:25 am
The execution of the procedure, "exec dbo.add_child 1, 'me', 86 " is just a test of that procedure, add_child. The procedure was intended to show how to test for the existence of a parent before adding the child row. The procedure definition is terminaged by the GO. The execution shows what you get from the RASIERRROR statement.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply