To laugh or cry...
Date: Sun, 12 Jul 2009 17:27:10 -0700 (PDT)
Message-ID: <c768d5bd-7875-4c02-b2e6-994cffb129a3_at_r36g2000vbn.googlegroups.com>
This is an extract from a board forum where I regularly participate to try to help other fellows about SQL. I have to admit that by the end of this thread the nature of the questions asked did make me feel like crying. In this thread the questionner is called al4629740 and the answerer is called momi_sabag. I let you judge for yourself.
> Questionner
Relate data in SQL
Question:
I have tables A and B. There are two columns in table A, that I
would like to have also in Table B. They need to relate. So if I
change the value in Table A, then Table B receives the change.
Is this called a relational table. How do I set that up in SQL Server
2005?
> momi_sabag:
you can do this by setting up a foreign key from table b to table a
and have that foreign key defined with update cascade
this means that each time the two columns in table a are updated, the same update will be applied to the matching rows in table b
alter table tableB add constraint myconstraint foreign key(col1, col2) references tablea (col1, col2) on update cascade
> al4629740:
Can this work between databases? Or does it only work within the
tables of one database?
> momi_sabag:
just tryif it does not work, you can create a simple trigger:
create trigger myTrigger on db1.tab1 after update as
update t2
set cola = t1.cola, colb = t1.colb
from updated t1 join db2.tab2 t2 on t1.id = t2.id
> al4629740:
What is "myconstraint"? What goes there?
> al4629740:
Maybe you can show me
:
My first table is frmProjectedBudget
Second table is frmProjectedBudget2
I need column "Last Name" "First Name in the first table to replicate over into the second table.
Is this correct:
alter table frmProjectedBudget2 add constraint myconstraint foreign key (Last Name, First Name) references frmProjectedBudget (Last Name, First Name) on update cascade
> momi_sabag:
you are correct
myConstraint is just the name you give to the constraint, you can put
there what ever you want
just make sure you surround the column names with [] since you have a
blank in the name
alter table frmProjectedBudget2 add constraint myconstraint foreign key
([Last Name], [First Name]) references frmProjectedBudget ([Last
Name], [First Name]) on update cascade
> al4629740:
I get the following error:
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table
'frmProjectedBudget' that match the referencing column list in the
foreign key 'myconstraint'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
what is wrong with my tables?
> momi_sabag:
you need to have either a primary key or a unique index defined on
columns (first_name,last_name) in frmProjectedBudget
> al4629740:
How does a relational database tables work? If I enter one data in
First Name on the Projected Budget table, does it automatically appear
in the other table?
Received on Mon Jul 13 2009 - 02:27:10 CEST