FKs don't publish?

I am using SQL2005 SP2 and noticed that the foreign keys don't publish
to the subscribers. Is this normal?
Thanks,
Peter Cwik
Peter,
It depends. Does this help?
http://technet.microsoft.com/en-us/library/ms180843.aspx
RLF
"PeterCwik" <pjcwik@.gmail.com> wrote in message
news:da63593c-961a-49e6-a511-96d7d60130ec@.k8g2000hsf.googlegroups.com...
>I am using SQL2005 SP2 and noticed that the foreign keys don't publish
> to the subscribers. Is this normal?
> Thanks,
> Peter Cwik
|||On Jan 3, 9:09Xam, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Peter,
> It depends. XDoes this help?http://technet.microsoft.com/en-us/library/ms180843.aspx
> RLF
> "PeterCwik" <pjc...@.gmail.com> wrote in message
> news:da63593c-961a-49e6-a511-96d7d60130ec@.k8g2000hsf.googlegroups.com...
>
>
> - Show quoted text -
Unfortunately, no. The foreign key is set to Yes for Enforce For
Replication.
|||you need to look at the article properties. There is an option to copy
foreign key constraints - make sure this is set to true.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"PeterCwik" <pjcwik@.gmail.com> wrote in message
news:da63593c-961a-49e6-a511-96d7d60130ec@.k8g2000hsf.googlegroups.com...
>I am using SQL2005 SP2 and noticed that the foreign keys don't publish
> to the subscribers. Is this normal?
> Thanks,
> Peter Cwik

FKs

Does anyone have a script that will evaluate the foreign key relationships in a db and generate a script to drop and re-create them?you might be able to write the sql procedure by using the sysforeignkeys table|||What will this be used for ?|||declare @.cr nchar(2)
declare @.go nvarchar(8)

set @.cr = nchar(13)+nchar(10)
set @.go = @.cr + 'GO' + @.cr

declare @.tablename nvarchar(128)
declare @.column nvarchar(128)
declare @.schema nvarchar(128)
declare @.constraint nvarchar(128)
declare @.fktable nvarchar(128)
declare @.fkconstraint nvarchar(128)
declare @.onupdate varchar(9)
declare @.ondelete varchar(9)
declare @.comma char(1)

declare @.sql nvarchar(4000)

declare cstrts cursor local fast_forward read_only for
select
c.[TABLE_SCHEMA],
c.[TABLE_NAME],
u.CONSTRAINT_NAME
from [INFORMATION_SCHEMA].[COLUMNS] c
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
on c.[TABLE_NAME] = u.[TABLE_NAME]
and c.[COLUMN_NAME] = u.[COLUMN_NAME]
inner join [INFORMATION_SCHEMA].[table_constraints] t
on u.[CONSTRAINT_NAME] = t.[CONSTRAINT_NAME]
where t.[CONSTRAINT_TYPE] = 'FOREIGN KEY'

open cstrts

fetch next from cstrts
into @.schema, @.tablename, @.constraint

while @.@.fetch_status = 0
begin
select
@.fktable = u2.[TABLE_NAME],
@.fkconstraint = r.[UNIQUE_CONSTRAINT_NAME],
@.onupdate = r.[UPDATE_RULE],
@.ondelete = r.[DELETE_RULE],
@.column = u.[COLUMN_NAME]
from [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] r
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u2
on r.[UNIQUE_CONSTRAINT_NAME] = u2.[CONSTRAINT_NAME]
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
on u.[CONSTRAINT_NAME] = r.[CONSTRAINT_NAME]
where r.[CONSTRAINT_NAME] = @.constraint

set @.sql =
'ALTER TABLE ['
+ @.schema
+ '].['
+ @.tablename
+ '] ADD CONSTRAINT ['
+ @.constraint
+ '] '
+ @.cr
+ 'FOREIGN KEY (['
+ @.column
+ ']) REFERENCES ['
+ @.fktable
+ '] ('

-- for each ordinal in the foreign key index...
declare idx cursor local fast_forward read_only for
select c.[COLUMN_NAME]
from [INFORMATION_SCHEMA].[COLUMNS] c
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
on c.[TABLE_NAME] = u.[TABLE_NAME]
and c.[COLUMN_NAME] = u.[COLUMN_NAME]
where u.[CONSTRAINT_NAME] = @.fkconstraint
order by u.[ORDINAL_POSITION]

open idx

set @.comma = ''

fetch next from idx
into @.column

while @.@.fetch_status = 0
begin
set @.sql = @.sql + @.comma + '[' + @.column + ']'
set @.comma = ','

fetch next from idx
into @.column

end

close idx
deallocate idx

set @.sql = @.sql + ') ON DELETE '
+ @.ondelete
+ ' ON UPDATE '
+ @.onupdate
+ @.go

print @.sql

fetch next from cstrts
into @.schema, @.tablename, @.constraint


end

close cstrts
deallocate cstrts
-------

See also my attachment to 'Restoring Databases from Win2000 to Win2003/Collation' which uses a drop and recreate to all forms of indexes to recollate a databse.

Hope This helps

HH|||Thanx, this is going to be used in conjunction with a proc that truncates all of the user tables in the db. I'm building a new system and need to frequently purge the data in the db to test data migration scripts and delete statements take too long to execute.

FK from other database

how to solve that problem?
i have 2 databases, in few tables from one database i need foreign keys from
other databases.Cant do it. You could try using triggers and stored procedures to check for
consistency but you can't use FK across databases.
MC
"TomislaW" <tomislav147@.hotmail.com> wrote in message
news:OjfFmHtAFHA.1300@.TK2MSFTNGP14.phx.gbl...
> how to solve that problem?
> i have 2 databases, in few tables from one database i need foreign keys
> from other databases.
>

fk constraints question

any way to turn off all foreign keys in a database and then turn them
back on after a few update statements are run?for each table you'd have to
alter table mytable nocheck constraint all
This will turn off check and FK constraints on the table... you could
combine this with
sp_msforeachtable ( if I remember the name correctly )
or generate the script ie
select 'alter table ' + name + ' nocheck constraint all' from
sysobjects where type = 'u' and id > 100
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ch" <ch@.dontemailme.com> wrote in message
news:416BC63E.F3CE4029@.dontemailme.com...
> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?|||You can use ALTER TABLE on a table that has foreign key
constraints to disable the constraint, e.g.
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name
You can find more information and an example in books online
under ALTER TABLE.
-Sue
On Tue, 12 Oct 2004 06:55:42 -0500, ch <ch@.dontemailme.com>
wrote:
>any way to turn off all foreign keys in a database and then turn them
>back on after a few update statements are run?|||Hi,
SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
And later...
SP_MSFOREACHTABLE 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Hermilson Tinoco.
"ch" wrote:
> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?
>

fk constraints question

any way to turn off all foreign keys in a database and then turn them
back on after a few update statements are run?
for each table you'd have to
alter table mytable nocheck constraint all
This will turn off check and FK constraints on the table... you could
combine this with
sp_msforeachtable ( if I remember the name correctly )
or generate the script ie
select 'alter table ' + name + ' nocheck constraint all' from
sysobjects where type = 'u' and id > 100
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ch" <ch@.dontemailme.com> wrote in message
news:416BC63E.F3CE4029@.dontemailme.com...
> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?
|||You can use ALTER TABLE on a table that has foreign key
constraints to disable the constraint, e.g.
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name
You can find more information and an example in books online
under ALTER TABLE.
-Sue
On Tue, 12 Oct 2004 06:55:42 -0500, ch <ch@.dontemailme.com>
wrote:

>any way to turn off all foreign keys in a database and then turn them
>back on after a few update statements are run?
|||Hi,
SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
And later...
SP_MSFOREACHTABLE 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Hermilson Tinoco.
"ch" wrote:

> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?
>

fk constraints question

any way to turn off all foreign keys in a database and then turn them
back on after a few update statements are run?for each table you'd have to
alter table mytable nocheck constraint all
This will turn off check and FK constraints on the table... you could
combine this with
sp_msforeachtable ( if I remember the name correctly )
or generate the script ie
select 'alter table ' + name + ' nocheck constraint all' from
sysobjects where type = 'u' and id > 100
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ch" <ch@.dontemailme.com> wrote in message
news:416BC63E.F3CE4029@.dontemailme.com...
> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?|||You can use ALTER TABLE on a table that has foreign key
constraints to disable the constraint, e.g.
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name
You can find more information and an example in books online
under ALTER TABLE.
-Sue
On Tue, 12 Oct 2004 06:55:42 -0500, ch <ch@.dontemailme.com>
wrote:

>any way to turn off all foreign keys in a database and then turn them
>back on after a few update statements are run?|||Hi,
SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
And later...
SP_MSFOREACHTABLE 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Hermilson Tinoco.
"ch" wrote:

> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?
>

FK Checks

Suppose you have two (or more) tables with foreign key constraints. My
question is thus:

Is it better to check if the fk exists before you try to perform the
insert or let SQL do it for you?

On one hand, if you check yourself and the key does not exist you can
gracefully handle it (maybe exit out of method with error). If you let
SQL do it, the server will throw an error which cannot be suppressed.

On the performance side, you doing the check will incur a slight (VERY
slight) hit since SQL will ALSO check anyways.Jason (JayCallas@.hotmail.com) writes:
> Suppose you have two (or more) tables with foreign key constraints. My
> question is thus:
> Is it better to check if the fk exists before you try to perform the
> insert or let SQL do it for you?

Depends on business requirements. Basically, Fkeys is the database's
mean of protection against bad data. If the user interface does not
perform any checks itself, and relies on the database, you may avoid
integrity violations, but the users may not get adequate error message.

The way I see database constraints constitutes an inner defense line.
The user interface should help the user, and have its own defense line.
Error messages from the database exposed to the user, should be considered
a bug.

But validation in a GUI, can be quite different from the validation in
SQL Server. Normally you don't let the user to type in the FK as free-
text, but you let him choose from a drop-down box or from a search screen.
Once you know, the user has selected data this way, you can assume that
you are safe. (Although, someone may delete the row before the users
saves.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns944C1D0487E5Yazorman@.127.0.0.1>...
> Depends on business requirements. Basically, Fkeys is the database's
> mean of protection against bad data. If the user interface does not
> perform any checks itself, and relies on the database, you may avoid
> integrity violations, but the users may not get adequate error message.
> The way I see database constraints constitutes an inner defense line.
> The user interface should help the user, and have its own defense line.
> Error messages from the database exposed to the user, should be considered
> a bug.
> But validation in a GUI, can be quite different from the validation in
> SQL Server. Normally you don't let the user to type in the FK as free-
> text, but you let him choose from a drop-down box or from a search screen.
> Once you know, the user has selected data this way, you can assume that
> you are safe. (Although, someone may delete the row before the users
> saves.)

I should have been more explicit in my question. I was referring to
performing checks within stored procedures.

Assuming Table2 has a FK relationship with Table1.

IF EXISTS (SELECT * FROM Table1 WHERE Table1.Field1 = @.field1)
INSERT INTO Table2 (@.field1, @.field2, field3)

as opposed to

INSERT INTO Table2 (@.field1, @.field2, field3)
IF @.@.Error RAISERROR('Unable to insert into Table2 because of missing
constraint', 15, 1)|||Jason (JayCallas@.hotmail.com) writes:
> I should have been more explicit in my question. I was referring to
> performing checks within stored procedures.
> Assuming Table2 has a FK relationship with Table1.
> IF EXISTS (SELECT * FROM Table1 WHERE Table1.Field1 = @.field1)
> INSERT INTO Table2 (@.field1, @.field2, field3)
> as opposed to
> INSERT INTO Table2 (@.field1, @.field2, field3)
> IF @.@.Error RAISERROR('Unable to insert into Table2 because of missing
> constraint', 15, 1)

Again it depends. Say that your procedure is to be called from a GUI. In
this case you can presume that the GUI gets the information from the
databaes, and a foreign-key violation could only occur if the GUI is
incorrect. On the other hand, say that you are accepting data from an
external source over which you have no control. In this case, it is better
to check explicitly, so that you can log incorrect data appropriately.
Recall that when a constraint blows up, you cannot in T-SQL determine
which constraint that fired.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

FK

I know that in the case of a primary key (non-clustered)
the index part of the foreign key must be stored
separately from the table. Is this true if you have a
foreign key on a table? Is there data stored externally
to the table for a foreign key?
Also: uou don't have to defrag or reindex a foreign key
do you?A foreign key is just a rule. SQL Server doesn't have to store anything
additional as you add the FK constraint. SQL Server will, however, make sure
that your data adheres to your rules, so a check is performed as you modify
data. Creating an index on the foreign key column can be very beneficial to
assist for this validation. Not to mention that you often perform joins over
primary key - foreign key relationships. And remember that a FK column can
be a good candidate for the clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"GXX" <anonymous@.discussions.microsoft.com> wrote in message
news:442201c4020f$c3ca8150$a601280a@.phx.gbl...
> I know that in the case of a primary key (non-clustered)
> the index part of the foreign key must be stored
> separately from the table. Is this true if you have a
> foreign key on a table? Is there data stored externally
> to the table for a foreign key?
> Also: uou don't have to defrag or reindex a foreign key
> do you?

FK

I know that in the case of a primary key (non-clustered)
the index part of the foreign key must be stored
separately from the table. Is this true if you have a
foreign key on a table? Is there data stored externally
to the table for a foreign key?
Also: uou don't have to defrag or reindex a foreign key
do you?A foreign key is just a rule. SQL Server doesn't have to store anything
additional as you add the FK constraint. SQL Server will, however, make sure
that your data adheres to your rules, so a check is performed as you modify
data. Creating an index on the foreign key column can be very beneficial to
assist for this validation. Not to mention that you often perform joins over
primary key - foreign key relationships. And remember that a FK column can
be a good candidate for the clustered index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"GXX" <anonymous@.discussions.microsoft.com> wrote in message
news:442201c4020f$c3ca8150$a601280a@.phx.gbl...
> I know that in the case of a primary key (non-clustered)
> the index part of the foreign key must be stored
> separately from the table. Is this true if you have a
> foreign key on a table? Is there data stored externally
> to the table for a foreign key?
> Also: uou don't have to defrag or reindex a foreign key
> do you?