Thursday, March 29, 2012
Foreign key
I need to add a column to a table. The new column is a foreign key which references another table's column.
how can you do this? Does it need to be done in two steps, like:
alter table table1
add new_col_name datatype
then
alter table table1
add table constraint.
If so, what is the syntax for adding a foreign key contraint which references another table.
thank youHello,
Yes, it has to be done in two steps, just as you said.
To add a foreign key constraint :
ALTER TABLE table1
ADD CONSTRAINT fk_table1_table2
FOREIGN KEY (field1)
REFERENCES table2(field2);
Where field1 is the column in table1 which is referenced by the column field2 in table2. If you have multi-column FK constraints, just put them in the right order, separated by commas :
ALTER TABLE table1
ADD CONSTRAINT fk_table1_table2
FOREIGN KEY (field11, field12)
REFERENCES table2(field21, field22);
Regards,
RBARAER|||thanks! what does the fk_table1_table2 mean? Is it just a lable?|||It can be done in one step, at least it can on Oracle:
alter table table1
add (new_col_name references table2(keycol));
or (to give the constraint a specific name):
alter table table1
add (new_col_name constraint table1_table2_fk references table2(keycol));|||cool, I'll try that also, but the first code worked.
Friday, February 24, 2012
For MSFT: Change of behaviour in configurations in SP1?
In SSIS SP1, if you use a .dtsconfig file that references a non-existent object in your package then you get a warning.
I don't have a pre-SP1 install to hand so I can't test it out but I seem to remember that pre-SP1 this used to cause an error.
Can anyone confirm that this behaviour has been changed in SP1?
Thanks
Jamie
Anyone?|||
Information: 0x40016041 at ConfigTest: The package is attempting to configure from the XML file "C:\Yukon\ISPlaying\ISPlaying\ConfigTest.dtsConfig".
Warning: 0x80012017 at ConfigTest: The package path referenced an object that cannot be found: "\Package.Variables[User::VariableX].Properties[Value]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
SSIS package "ConfigTest.dtsx" starting.
SSIS package "ConfigTest.dtsx" finished: Success.
Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00
Your memory is not up to it I'm afraid, a sad thing to see in a man of your age. :)
|||I seem to remember that you thought the same as me on Friday
Oh, and I'll always be younger than you!!!
-Jamie
Sunday, February 19, 2012
For CTE
ProdStruct is my CTE Name
I got an Error likeRecursive member of a common table expression 'ProdStruct' has multiple recursive references.
Give me SolutionCan you post the query as well as DDL for the objects involved in it?|||Can you post the query as well as DDL for the objects involved in it?