Foreign Key and Nullable column.

Hi Gurus,
This seems simple but not to me. Now I want to create a new table that
contains a column such as Product_Id. I want to create a foreign key
constraint on Product_Id pointing to a Product table. Sometimes the
Product_Id can be NULL but I don't want to make the column to be a nullable
column, because that can make query complex and slow. So I like to use 0 to
represent NULL. Now I also don't want to insert a Product_Id=0 to the Product
table, because I just worry that can hurt applications or some reports. My
dillema here is that I just can't create the forieign key to protect data
integrity because the 0 Product_Id may not be able to find a line in the
Product table. I also have other design choices like this.
Do you have similar experience? Can any guru here give me a good advice? Can
I avoid to insert a dummy line with Product_Id=0 and still have the
Non-nullable column and still can create the FK?
Thanks in advance.
JamesJames,
Could make the column NOT NULL with a DEFAULT constraint of 9999999 or some
number out-of-range for your products.
HTH
Jerry
"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:0829A5B5-7BCE-4508-96E9-24ADA978A88D@.microsoft.com...
> Hi Gurus,
> This seems simple but not to me. Now I want to create a new table that
> contains a column such as Product_Id. I want to create a foreign key
> constraint on Product_Id pointing to a Product table. Sometimes the
> Product_Id can be NULL but I don't want to make the column to be a
> nullable
> column, because that can make query complex and slow. So I like to use 0
> to
> represent NULL. Now I also don't want to insert a Product_Id=0 to the
> Product
> table, because I just worry that can hurt applications or some reports.
> My
> dillema here is that I just can't create the forieign key to protect data
> integrity because the 0 Product_Id may not be able to find a line in the
> Product table. I also have other design choices like this.
> Do you have similar experience? Can any guru here give me a good advice?
> Can
> I avoid to insert a dummy line with Product_Id=0 and still have the
> Non-nullable column and still can create the FK?
> Thanks in advance.
> James|||James Ma wrote:
> Hi Gurus,
> This seems simple but not to me. Now I want to create a new table that
> contains a column such as Product_Id. I want to create a foreign key
> constraint on Product_Id pointing to a Product table. Sometimes the
> Product_Id can be NULL but I don't want to make the column to be a
> nullable column, because that can make query complex and slow. So I
> like to use 0 to represent NULL. Now I also don't want to insert a
> Product_Id=0 to the Product table, because I just worry that can hurt
> applications or some reports. My dillema here is that I just can't
> create the forieign key to protect data integrity because the 0
> Product_Id may not be able to find a line in the Product table. I
> also have other design choices like this.
> Do you have similar experience? Can any guru here give me a good
> advice? Can I avoid to insert a dummy line with Product_Id=0 and
> still have the Non-nullable column and still can create the FK?
> Thanks in advance.
> James
If the FK column can be null then allow null values. From a design
standpoint that's the best option to maintain data integrity, despite
whatever complications this causes with queries.
You can create a view to query the table and replace the NULL value with
whatever you want to send to the application. If no users have rights to
query the table directly you have what you want and have the RI on the
back end that the database demands.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks a lot. I'll use nullable column.
However, I still appreciate if you can help me to clarify. I read many
articles or books that recommend to use a special not null value to represent
null value, since null's behaviour is wierd sometimes (My experiences also
confirm that). So, does that mean it is a better idea to use the dummy line
method if I am designing a database from scratch? When no applications and no
queries have ever been coded?
"David Gugick" wrote:
> James Ma wrote:
> > Hi Gurus,
> >
> > This seems simple but not to me. Now I want to create a new table that
> > contains a column such as Product_Id. I want to create a foreign key
> > constraint on Product_Id pointing to a Product table. Sometimes the
> > Product_Id can be NULL but I don't want to make the column to be a
> > nullable column, because that can make query complex and slow. So I
> > like to use 0 to represent NULL. Now I also don't want to insert a
> > Product_Id=0 to the Product table, because I just worry that can hurt
> > applications or some reports. My dillema here is that I just can't
> > create the forieign key to protect data integrity because the 0
> > Product_Id may not be able to find a line in the Product table. I
> > also have other design choices like this.
> >
> > Do you have similar experience? Can any guru here give me a good
> > advice? Can I avoid to insert a dummy line with Product_Id=0 and
> > still have the Non-nullable column and still can create the FK?
> >
> > Thanks in advance.
> >
> > James
> If the FK column can be null then allow null values. From a design
> standpoint that's the best option to maintain data integrity, despite
> whatever complications this causes with queries.
> You can create a view to query the table and replace the NULL value with
> whatever you want to send to the application. If no users have rights to
> query the table directly you have what you want and have the RI on the
> back end that the database demands.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||James Ma wrote:
> Thanks a lot. I'll use nullable column.
> However, I still appreciate if you can help me to clarify. I read many
> articles or books that recommend to use a special not null value to
> represent null value, since null's behaviour is wierd sometimes (My
> experiences also confirm that). So, does that mean it is a better
> idea to use the dummy line method if I am designing a database from
> scratch? When no applications and no queries have ever been coded?
>
If there is a business case for this, and the value actually means
something, then you can do it. Otherwise, I wouldn't.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:9E1AF30B-B432-449D-9540-B541910D9F38@.microsoft.com...
> Thanks a lot. I'll use nullable column.
> However, I still appreciate if you can help me to clarify. I read many
> articles or books that recommend to use a special not null value to
> represent
> null value, since null's behaviour is wierd sometimes (My experiences also
> confirm that). So, does that mean it is a better idea to use the dummy
> line
> method if I am designing a database from scratch? When no applications and
> no
> queries have ever been coded?
>
Opinions differ and there aren't necessarily absolute right or wrong answers
to design questions. In my opinion it does make perfect sense to minimise
the use of nulls wherever feasible. I dislike using nullable foreign keys
for some of the reasons you've mentioned. There is an easy alternative.
Create a new table that has a common primary key with your current one and
then only populate that table where you need to reference a product.
CREATE TABLE your_table (x INTEGER NOT NULL PRIMARY KEY, ....)
CREATE TABLE your_table_product (x INTEGER NOT NULL PRIMARY KEY REFERENCES
your_table (x) , ...., product_id INTEGER NOT NULL REFERENCES products
(product_id))
--
David Portas
SQL Server MVP
--

Foreign key and indexes

I have a question about foreign keys and indexes that I am unsure
about. If a table has a foreign key does this column have a non
clustered index assigned to it as default (that is hidden)? or does it
make sense to add a non clustered index to the foreign key column in
the foreign key table?
I am thinking this as I am unsure how SQL server handles foreign keys.
EXAMPLE BELOW...
DOES TABLE2.Table1ID have a non clustered index that SQL server uses?
CREATE TABLE dbo.Table1
(
Table1Id int NOT NULL,
Foo varchar(10) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1_1 PRIMARY KEY CLUSTERED
(
Table1Id
) ON [PRIMARY]
GO
CREATE TABLE dbo.Table2
(
Table2ID int NOT NULL,
Table1ID int NULL,
barr varchar(10) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 ADD CONSTRAINT
PK_Table2 PRIMARY KEY CLUSTERED
(
Table2ID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 WITH NOCHECK ADD CONSTRAINT
FK_Table2_Table1 FOREIGN KEY
(
Table1ID
) REFERENCES dbo.Table1
(
Table1Id
) NOT FOR REPLICATION
GO
ALTER TABLE dbo.Table2
NOCHECK CONSTRAINT FK_Table2_Table1
GOSQL Server does not index foreign keys by default. Usually it does make
sense to create an index on a foreign key.
David Portas
SQL Server MVP
--sql

Foreign Key + Index

Imagine 2 tables:
AUTHORS
author_id (int) (PK)
author_name (varchar)
BOOKS
book_id (int) (PK)
book_author_id (int) (FK from AUTHOR)
book_title (varchar)
book_author_id is already declared as a foreign key.
If I want better performance when querying SELECT * FROM BOOKS WHERE
book_author_id = 1234
do I have to set a index on book_author_id,
or is it unecessary as a FK is already set?
I think that as a FK is a constraint and not an index, it's still necessary
but I want to be sure.
Can you answer my question?
Thanks
Henria Foreign Key is NOT automatically indexed in SQL Server.
you'll need to index it.
Greg Jackson
Portland, OR|||Thanks for your answer Greg :-)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> a écrit dans le message de
news:OMI0r%234EFHA.3200@.TK2MSFTNGP10.phx.gbl...
> a Foreign Key is NOT automatically indexed in SQL Server.
> you'll need to index it.
>
> Greg Jackson
> Portland, OR
>
>

Foreign Key + Index

Imagine 2 tables:
AUTHORS
author_id (int) (PK)
author_name (varchar)
BOOKS
book_id (int) (PK)
book_author_id (int) (FK from AUTHOR)
book_title (varchar)
book_author_id is already declared as a foreign key.
If I want better performance when querying SELECT * FROM BOOKS WHERE
book_author_id = 1234
do I have to set a index on book_author_id,
or is it unecessary as a FK is already set?
I think that as a FK is a constraint and not an index, it's still necessary
but I want to be sure.
Can you answer my question?
Thanks
Henri
a Foreign Key is NOT automatically indexed in SQL Server.
you'll need to index it.
Greg Jackson
Portland, OR
|||Thanks for your answer Greg :-)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> a crit dans le message de
news:OMI0r%234EFHA.3200@.TK2MSFTNGP10.phx.gbl...
> a Foreign Key is NOT automatically indexed in SQL Server.
> you'll need to index it.
>
> Greg Jackson
> Portland, OR
>
>

Foreign Key + Index

Imagine 2 tables:
AUTHORS
author_id (int) (PK)
author_name (varchar)
BOOKS
book_id (int) (PK)
book_author_id (int) (FK from AUTHOR)
book_title (varchar)
book_author_id is already declared as a foreign key.
If I want better performance when querying SELECT * FROM BOOKS WHERE
book_author_id = 1234
do I have to set a index on book_author_id,
or is it unecessary as a FK is already set?
I think that as a FK is a constraint and not an index, it's still necessary
but I want to be sure.
Can you answer my question?
Thanks
Henria Foreign Key is NOT automatically indexed in SQL Server.
you'll need to index it.
Greg Jackson
Portland, OR|||Thanks for your answer Greg :-)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> a crit dans le message de
news:OMI0r%234EFHA.3200@.TK2MSFTNGP10.phx.gbl...
> a Foreign Key is NOT automatically indexed in SQL Server.
> you'll need to index it.
>
> Greg Jackson
> Portland, OR
>
>

foreign key - relationship

I have two tables and I'm trying to create a one to many relationship (master table can have many records in the details table)

I created a column in my details table with with ID of the primary key in the master table.

The primary key ID isn't inserted as a foreign key when I insert a record. I specifed the relationship in EM.

Not sure why the primary key ID isn't inserted as a foreign key into my details table?

Any help is greatly appreciated. Thanks.
-Dman100-Explain how you are doing it?

ohhh you do realise you need to insert the value into the table -- it does not do it automatically|||Okay, my mistake, I thought it would be automatic.

Can I do this within my sql statement, using a join or insert or whatever?? to pass the primary key value from the master table into the details table as a foreign key?

Thanks for your help! I appreciate it.
-Dman100-

Foreign Key - ON DELETE RESTRICT

Hi

Are there any way to use foreign key in MS-SQL Express with ON DELETE RESTRICT like other databases?

Best Regards

Igor Sane

to my knowledge there are only the cacade update/delete functions|||

isane did this answer your question? if not please provide more info or mark answer.

thanks,

derek

sql

Foreign Key

I would like to create a foreign key but the Primary table has 2 fields as it Primary Key. Is there a way to create a Foreign Key that links only on one field of the primary key.

Ex: table 1: id int , language char(2), description varchar(100) PK = ID + language

table 2 : id int, idlanguage int PK = id FK (idLanguage refers to id from table 1)

This cause an error because the foreign key does not include all part of the primary key.

Rufen

If your table1.id is unique, then create primary key only on that column, if not, then you should add laguage in your table2 column because there will be no way to distinguish between languages that have the same id.
|||

I know that there will be no way to distinguish all records that have the same id, but that is what I want. When I delete a record from Table 1, I want to delete all record from table 2 that have this id (foreign from table 1).

|||

You can implement the foreign key logic using triggers.

For eg. For Delete

CREATE TRIGGER trg

ON table1

FOR DELETE

AS

BEGIN

DELETE FROM table2

WHERE idlanguage in (SELECT id FROM deleted)

END

You can have similar trigger for insert and update

foreign key

hi friends,
I want to check the relationship between tables before migration.
so i wrote a procedure which will push the unrelated data from the source
db(@.i_oldDB) to the error database(@.i_errorDb).
alter procedure TransactionValidation
(
@.i_oldDb varchar(100),
@.i_errorDb varchar(100),
@.i_ParentTable varchar(100),
@.i_ChildTable varchar(100),
@.i_PrimaryKey varchar(100),
@.i_ForeignKey varchar(100)
)
as
begin
Declare @.SQL nvarchar(4000)
select @.SQL = 'if exists (select * from ' + @.i_errorDb +
'.INFORMATION_SCHEMA.TABLES where ' +
'Table_Name like ' + CHAR(39) + @.i_ChildTable + CHAR(39) + ') drop table ' +
@.i_errorDb + '..'+ @.i_ChildTable
exec sp_executesql @.sql
select @.sql = 'SELECT * into ' + @.i_errorDb + '..' + @.i_ChildTable + ' from
'
+ @.i_oldDb + '..'+ @.i_ChildTable + ' where ' + @.i_oldDb + '..'+
@.i_ChildTable + '.' + @.i_ForeignKey + ' not in
(select ' + @.i_PrimaryKey + ' from ' + @.i_oldDb + '..'+ @.i_ParentTable + ')'
exec sp_executesql @.sql
select @.sql = 'delete from ' + @.i_oldDb + '..'+ @.i_ChildTable + ' where ' +
@.i_oldDb + '..'+ @.i_ChildTable + '.'
+ @.i_ForeignKey + ' not in
(select ' + @.i_PrimaryKey + ' from ' + @.i_oldDb + '..'+ @.i_ParentTable + ')'
exec sp_executesql @.sql
end
now my problem is, if i have multiple relationship column in the table...
this will not work. how to do this?
its very urgent.
pls help me to solve this.
thanks
vanithaThere are a couple of alternatives to NOT IN for composite keys. You could
use NOT EXISTS or an OUTER JOIN. The generated SQL would be something like
the untested examples below.
SELECT *
INTO MyErrorTable
FROM MyChildTable
WHERE NOT EXISTS
(
SELECT *
FROM MyParentTable
WHERE MyParentTable.Col1 = MyChildTable.Col1 AND
MyParentTable.Col2 = MyChildTable.Col2
)
SELECT MyChildTable.*
INTO MyErrorTable
FROM MyChildTable
LEFT OUTER JOIN MyParentTable ON
MyParentTable.Col1 = MyChildTable.Col1 AND
MyParentTable.Col2 = MyChildTable.Col2
WHERE MyParentTable.Col1 IS NULL
Hope this helps.
Dan Guzman
SQL Server MVP
"vanitha" <vanitha@.discussions.microsoft.com> wrote in message
news:710A7736-41F8-478F-BFFF-3AA86A31FFE0@.microsoft.com...
> hi friends,
> I want to check the relationship between tables before migration.
> so i wrote a procedure which will push the unrelated data from the source
> db(@.i_oldDB) to the error database(@.i_errorDb).
> alter procedure TransactionValidation
> (
> @.i_oldDb varchar(100),
> @.i_errorDb varchar(100),
> @.i_ParentTable varchar(100),
> @.i_ChildTable varchar(100),
> @.i_PrimaryKey varchar(100),
> @.i_ForeignKey varchar(100)
> )
> as
> begin
> Declare @.SQL nvarchar(4000)
> select @.SQL = 'if exists (select * from ' + @.i_errorDb +
> '.INFORMATION_SCHEMA.TABLES where ' +
> 'Table_Name like ' + CHAR(39) + @.i_ChildTable + CHAR(39) + ') drop table '
> +
> @.i_errorDb + '..'+ @.i_ChildTable
> exec sp_executesql @.sql
> select @.sql = 'SELECT * into ' + @.i_errorDb + '..' + @.i_ChildTable + '
> from
> '
> + @.i_oldDb + '..'+ @.i_ChildTable + ' where ' + @.i_oldDb + '..'+
> @.i_ChildTable + '.' + @.i_ForeignKey + ' not in
> (select ' + @.i_PrimaryKey + ' from ' + @.i_oldDb + '..'+ @.i_ParentTable +
> ')'
>
> exec sp_executesql @.sql
> select @.sql = 'delete from ' + @.i_oldDb + '..'+ @.i_ChildTable + ' where '
> +
> @.i_oldDb + '..'+ @.i_ChildTable + '.'
> + @.i_ForeignKey + ' not in
> (select ' + @.i_PrimaryKey + ' from ' + @.i_oldDb + '..'+ @.i_ParentTable +
> ')'
> exec sp_executesql @.sql
>
> end
> now my problem is, if i have multiple relationship column in the table...
> this will not work. how to do this?
> its very urgent.
> pls help me to solve this.
> thanks
> vanitha
>

Foreign key

Hello,
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.

foreign key

I want to make a foreign key relationship between two tables but the key is
multiple fields. I am getting an error message when I try.
'''''?Can you post DDL for your tables and the code you're trying to use to create
the foreign key constraint?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <sql@.discussions.microsoft.com> wrote in message
news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> I want to make a foreign key relationship between two tables but the key
is
> multiple fields. I am getting an error message when I try.
> '''''?|||ALTER TABLE SecondaryTableName
ADD CONSTRAINT ConstraintName
FOREIGN KEY (ForeignKeyColumns)
REFERENCES dbo.PrimaryTable (PrimaryKeyColumnName)
Be sure to list the composite columnc in the same order.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23S3OJUNyEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Can you post DDL for your tables and the code you're trying to use to
> create
> the foreign key constraint?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "sql" <sql@.discussions.microsoft.com> wrote in message
> news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
>> I want to make a foreign key relationship between two tables but the key
> is
>> multiple fields. I am getting an error message when I try.
>> '''''?
>|||Script and error message
ALTER TABLE MNP_MINE_PROD
ADD CONSTRAINT FK_TEST
FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
'FK_TEST'. The conflict occurred in database 'S_DEV', table 'MOR_MINE_OP_RPT'.
"sql" wrote:
> I want to make a foreign key relationship between two tables but the key is
> multiple fields. I am getting an error message when I try.
> '''''?|||You have some rows in MNP_MINE_PROD that aren't in MOR_MINE_OP_RPT. So the
FK can't be created... try this:
SELECT *
FROM MNP_MINE_PROD A
WHERE NOT EXISTS
(SELECT *
FROM MOR_MINE_OP_RPT B
WHERE A.MNE_ID = B.MNE_ID
AND A.MOR_YEAR=B.MOR_YEAR
AND A.ORT_ID = B.ORT_ID)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <sql@.discussions.microsoft.com> wrote in message
news:57509E6C-7118-4B0C-A0DF-B3EF4FCF9464@.microsoft.com...
> Script and error message
> ALTER TABLE MNP_MINE_PROD
> ADD CONSTRAINT FK_TEST
> FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
> REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_TEST'. The conflict occurred in database 'S_DEV', table
'MOR_MINE_OP_RPT'.
>
> "sql" wrote:
> > I want to make a foreign key relationship between two tables but the key
is
> > multiple fields. I am getting an error message when I try.
> > '''''?

Foreign key

How do I find out Foreign keys associated with a table?
How do I create Foreign Key when creating a table
using Enterprise manager.
Thank you,
Lin> How do I find out Foreign keys associated with a table?
http://www.aspfaq.com/2520
> How do I create Foreign Key when creating a table
> using Enterprise manager.
I recommend creating tables in Query Analyzer, then you can explicitly
declare foreign keys instead of using a GUI. See CREATE TABLE in Books
Online.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||sp_help <table name> will show you the foreign keys associated with a give
table.
To add foreign key relationships in EM, click on the "Manage
Relationship..." toolbar item when creating a "New Table".
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
> How do I find out Foreign keys associated with a table?
> How do I create Foreign Key when creating a table
> using Enterprise manager.
> Thank you,
> Lin
>|||Greg,
Thank you very much, this really helps!
-Lin
>--Original Message--
>sp_help <table name> will show you the foreign keys
associated with a give
>table.
>To add foreign key relationships in EM, click on
the "Manage
>Relationship..." toolbar item when creating a "New
Table".
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Lin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
>> How do I find out Foreign keys associated with a table?
>> How do I create Foreign Key when creating a table
>> using Enterprise manager.
>> Thank you,
>> Lin
>
>.
>|||Aaron,
Thank you very much!
-Lin
>--Original Message--
>> How do I find out Foreign keys associated with a table?
>http://www.aspfaq.com/2520
>> How do I create Foreign Key when creating a table
>> using Enterprise manager.
>I recommend creating tables in Query Analyzer, then you
can explicitly
>declare foreign keys instead of using a GUI. See CREATE
TABLE in Books
>Online.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>sql

Foreign Key

Hi Friends,
Is there any way to get the table name which is referenced by the
foreign key

for example: consider two table "Staff" and "Department"

Staff with following columns
PK_ID
FK_DepartmentID
Name
Address

Department with following columns
PK_DepartmentID
DeptName

Actually what i need is: Initially i would be having the table name as
"Staff"
from Staff table i need to identify that the column FK_DepartmentID is
a foreign key
and the primary key is in the Department table

i need to traverse from Staff table and identify that FK_DepartmentID
is a primary key in Department table

this has to be accomplished by sql query... probably this could be
fetched from
Data Dictionary but i couldnt find the relationship between the system
tables.

Thanks
ArunDhaJArunDhaJ wrote:

Quote:

Originally Posted by

Hi Friends,
Is there any way to get the table name which is referenced by the
foreign key
(..)

(SQL Server 2005)

IMHO the easiest way is to use sys.foreign_keys. You don't need any
other system view. Try this:

USE YOUR_DATABASE; -- remember about current database context

SELECT
OBJECT_NAME(parent_object_id) as table_with_FK,
OBJECT_NAME(referenced_object_id) as referenced_table
FROM sys.foreign_keys
WHERE OBJECT_NAME(parent_object_id) = 'Staff'

--
Best regards,
Marcin Guzowski
http://guzowski.info

foreign key

Hi

is it a good practice to create FK on each & every column in a table whose values are taken from master table.

should I create or not.

If I create will that be heavy i.e what about memory consumption ?

pls reply

Thanks

Shubhangi1. You can foreign keys.

or

2. You can check through code. By using Inner join table name
on parenttabel.field=childtable.field

ro

3.Before inserting a record in child table, check whether it exists in the parent table through code by using if exists or if (select count(*) from tablename where
...)=0

or

if exists(select 1 from tablename where ...)

Foreign Key

how can i create a foreign key which spans on 3 columns.

Quote:

Originally Posted by NaimishGohil

how can i create a foreign key which spans on 3 columns.

Can u explain your problem a bit more. and can u provide some structure that u want to create.

Mandy

Foreign Key

Hi,

Is sql server performance affected with 7 to 8 FK on a single master table.

Thanks
ShubhangiNot at all

Foreign Key

I would like to create a foreign key but the Primary table has 2 fields as it Primary Key. Is there a way to create a Foreign Key that links only on one field of the primary key.

Ex: table 1: id int , language char(2), description varchar(100) PK = ID + language

table 2 : id int, idlanguage int PK = id FK (idLanguage refers to id from table 1)

This cause an error because the foreign key does not include all part of the primary key.

Rufen

If your table1.id is unique, then create primary key only on that column, if not, then you should add laguage in your table2 column because there will be no way to distinguish between languages that have the same id.
|||

I know that there will be no way to distinguish all records that have the same id, but that is what I want. When I delete a record from Table 1, I want to delete all record from table 2 that have this id (foreign from table 1).

|||

You can implement the foreign key logic using triggers.

For eg. For Delete

CREATE TRIGGER trg

ON table1

FOR DELETE

AS

BEGIN

DELETE FROM table2

WHERE idlanguage in (SELECT id FROM deleted)

END

You can have similar trigger for insert and update

sql

foreign key

i want to make primary key and foreign key relationship of table A1 and table B1 but A1 exist in database A and B1 exist in database B
column name u can pretain as C1, C2

'Normally', related tables live within the same database.

The Foreign Key constraint declaration doesn't go outside the scope of the database, so in this case you can't declare a FK constraint.

AFAIK, the option you have to enforce cross-database FK relationships, is by using triggers.

/Kenneth

|||

You can’t create a constraint across the database. But there is a workaround available to fix your issue. Using Instead of trigger / for after trigger. But I recommend to use the Instead of Trigger rather than after trigger..

Code Snippet

Use DB1

Go

Create table A

(

ID int Primary Key,

Name varchar(100)

)

Go

Code Snippet

Use DB2

Go

Create table BB

(

Id int,

[Desc] varchar(100)

)

Go

CreateTrigger BB_Triger

on BBInstead of Insert

as

Begin

Insert Into BB

Select * from Inserted as ins Where Exists (Select 1 From DB1..A a Where a.id = ins.id)

End

/*

--use any one

Create Trigger BB_Triger

on BBAfter Insert

as

Begin

Delete from BB

Where NOT EXISTS (Select 1 From DB1..A a Where a.id = BB.id)

End

*/

GO

Code Snippet

Insert Into DB1..A values(1,'One')

Insert Into DB2..A values(2,'Two')

Code Snippet

Insert Into DB2..BB values(1,'Valid')

select * from BB

Insert Into DB2..BB values(4,'In Valid')

select * from BB

|||Thanks good idea

foreign key

Hello,

I want to make a poll and have to use a foreign key in my database. This is not possible in Web Matrix so I have to use SQL Enterprise Manager. But don't know how. I read the help but can't figure it out. Can someone help me? Thanks in advance.

Regards,

Roel AlblasHi Roel,

I'm not quite sure what you mean. Do you need to know how to write the SQL statement that uses a foreign key? Or create tables with a foreign key?

Tell us more about what you want to do and we'll try to help.

Don|||I'm making a poll following an example. In that example I have to make tables wich has an relation to each other with a foreing key. I use SQL Server 2000 and Web Matrix.

Roel|||So you're building a table. And you'll use EM to do it. Okay.

Here's a simple example. Since I don't know the particulars of the data you'll use, I'll use a simple contact management example, where each person can have multiple phone numbers.

The Person table would look something like this:

PersonID int (identity, primary key)
Name varchar(30)
...

The Phone table would look something like this:

PhoneID int (identity, primary key)
PhoneNumber varchar(15)
PersonID int
... (type of number, etc.)

In this case, I've named the linking field, PersonID, the same in each table but that is not necessary. You may want to use a naming standard that identifies both primary and foreign keys in your tables. Note in particular that Phone.PersonID isnot an identity field, because it can have duplicate data when a person has several phone numbers.

Using this structure you can now do joins on the two tables to return all of the numbers for a person, or a list of everyone and their phone numbers.

If you want the database to enforce referential integrity (make sure that there are no phone numbers without a person, cascade deletes, etc.) you can also create a relationship between the tables. The easiest way to do this in EM is to create a database diagram with the two tables and create it visually.

Is this enough information? If not, ask away.

Don|||Hi

Just wondering if you know of a similar feature in web matrix to create relationships?
I am using MSDE?

Thanks

Ramila|||No, I sure don't. It's been a while since I did a project with Web Matrix.

There are some other admin tools available, such as these, but I don't know their capabilities for creating relationships:

ASP.NET Enterprise Manager, an open source SQL Server and MSDE management tool.

Microsoft's Web Data Administrator is a free web-based MSDE management program written using C# and ASP.NET, and includes source code.

You can also use T-SQL through the osql command-line utility to create or modify your tables.

How are you creating the structure of your database? Through Matrix? Another way?

Don

Foreign key

How do I find out Foreign keys associated with a table?
How do I create Foreign Key when creating a table
using Enterprise manager.
Thank you,
Lin
> How do I find out Foreign keys associated with a table?
http://www.aspfaq.com/2520

> How do I create Foreign Key when creating a table
> using Enterprise manager.
I recommend creating tables in Query Analyzer, then you can explicitly
declare foreign keys instead of using a GUI. See CREATE TABLE in Books
Online.
http://www.aspfaq.com/
(Reverse address to reply.)
|||sp_help <table name> will show you the foreign keys associated with a give
table.
To add foreign key relationships in EM, click on the "Manage
Relationship..." toolbar item when creating a "New Table".
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
> How do I find out Foreign keys associated with a table?
> How do I create Foreign Key when creating a table
> using Enterprise manager.
> Thank you,
> Lin
>
|||Greg,
Thank you very much, this really helps!
-Lin

>--Original Message--
>sp_help <table name> will show you the foreign keys
associated with a give
>table.
>To add foreign key relationships in EM, click on
the "Manage
>Relationship..." toolbar item when creating a "New
Table".
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Lin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
>
>.
>
|||Aaron,
Thank you very much!
-Lin

>--Original Message--
>http://www.aspfaq.com/2520
>
>I recommend creating tables in Query Analyzer, then you
can explicitly
>declare foreign keys instead of using a GUI. See CREATE
TABLE in Books
>Online.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>