Foreign Key Constraints

They are all created with the 'NOT FOR REPLICATION' option but that doesn't seem to help at all.

try
alter table <YourTable> nocheck constraint all

Tried that.  I get this error:

Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'clients' because it is being referenced by a FOREIGN KEY constraint.

Yes you must disable constraints on the tables that reference 'clients',
not on the clients table

back with a script in a few minutes

replace 'ref_region' (the table name i used to test)
with the name of the table you need to truncate

HTH

Hilaire

Hello,

1.  Firstly, you cannot disable a constraint but only DROP it.

2.  You can use delete instead of truncate as truncate is a DDL statement (that's why it's failing on the DRI violation).


VC

VC, I'm puzzled

>>you cannot disable a constraint <<
what does the statement
'alter table <YourTable> nocheck constraint all'
do then ???

>>You can use delete instead of truncate as truncate is a DDL statement (that's why it's failing on the DRI violation).<<
using truncate in SPs/T-sql scripts was not allowed in old versions of sql server and in sybase -> V11 at least
it's allowed in recent versions of SQL Server, and much faster than delete in most cases
why the heck would it raise an error ?

@JackieLee
Did my script help ?

Regards

Hilaire

Typo in my script (typed 'uncheck all' instead of 'nocheck all')

declare @i int, @rc int, @sql varchar(250)
create table #temp (id int identity, table_name nvarchar(128), constraint_name nvarchar(128))
insert into #temp
select  object_name(fkeyid) ,  object_name(constid)
from sysreferences where rkeyid = object_id(<YourTableNameHere>)
set @rc = @@rowcount
set @i = 1
while @i <= @rc
begin
     select @sql = 'alter table ' + table_name + ' nocheck constraint all ' from #temp where id = @i
     exec (@sql)
     set @i = @i + 1    
end
drop table #temp

@Hillaire:

From BOL:

"When disabled, future inserts or updates to the column are not validated against the constraint conditions"

truncate is not a DML,  it's a DDL = drop table+ create table.

You cannot disable DRI either for drop or truncate.

In Yukon,  you cannot disable DRI for deletions either.

"it's allowed in recent versions of SQL Server, and much faster than delete in most cases
why the heck would it raise an error ?
"

Again,  because it's not just fast delete -- it's a DDL statement like create table.

VC

This works:

1> drop table c
2> go
1> drop table p
2> go
1> create table p(id int primary key)
2> go
1> create table c(data int, id int constraint fk foreign key (id) references p (id))
2> go
1> insert into p values(1)
2> insert into p values(2)
3> insert into p values(3)
4> insert into c values(10, 1)
5> insert into c values(20, 3)
6> go

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2> alter table c nocheck constraint fk
3> delete from p
4> go

(3 rows affected)


... and this does not:

1> drop table c
2> go
1> drop table p
2> go
1> create table p(id int primary key)
2> go
1> create table c(data int, id int constraint fk foreign key (id) references p (id))
2> go
1> insert into p values(1)
2> insert into p values(2)
3> insert into p values(3)
4> insert into c values(10, 1)
5> insert into c values(20, 3)
6> go

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2> alter table c nocheck constraint fk
3> truncate table p
4> go
Msg 4712, Level 16, State 1, Server STARGUS-DAVER, Line 3
Microsoft OLE DB Provider for SQL Server, Cannot truncate table 'p' because it i
s being referenced by a FOREIGN KEY constraint.
1>


VC

@VC
so you agree that constraints CAN be disabled,
that was not obvious in your first post

and my script can be useful as well

I agree that truncate cannot be used in this situation

Hilaire

@Hilaire,

I should have qulaified my statement:

1.  Firstly, you cannot disable a constraint but only DROP it for DDL operations.

... but I thought it was obvious from the context.

The point is the original poster cannot use truncate on the parent tables with DRI at all.  He/she has to use delete.


Rgds.

VC

I haven't read all posts...but here some thoughts...

why don't you use the cacading actions (look in bol for them)
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]

If you choose on delete cascade, then whenever you will delete from a table, all the records referencing the deleted records, will also be deleted... this is what you want to achive ? (so you will be able to delete records from the tables)

if the purpose of FK is the fact to not be able to delete records that are referenced, than this option is not good for you.

regards,
 xenon

pS: for data load....you will have to load records in a certain order, so the error will not appear, first the referenced tables, than the others.... because droping than recreating the FK seems too strange for me

xenon,

I am using cascading delete.  That's my only reason for creating these constraints.  

All,

I'll try this stuff out today.  I need to be able to truncate tables when installing my database for new clients.

I also tried deleting the contents of the table and i get the following error:

Server: Msg 512, Level 16, State 1, Procedure trig_StopClientDelete, Line 9
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.


This error makes no sense to me because there is no subquery.  The query I used is 'Delete From Clients' and there are no triggers on the table.

Run

EXEC sp_help 'trig_StopClientDelete'

Woops.  Forgot to drop the instead of trigger.  I only dropped the after trigger.  delete works fine thanks.

https://www.experts-exchange.com/questions/21114662/enforce-relationship-for-replication.html

Too much work! Just delete the table contents  (referencers first then referencees) and then reset the identity counter. e.g., two tables, tblSampleUnit has a foreign key referencing tblSample, I clear them thusly:

PRINT 'Clearing tblSampleUnit'
DELETE FROM tblSampleUnit
DBCC CHECKIDENT (tblSampleUnit, RESEED, 0)

PRINT 'Clearing tblSample'
DELETE FROM WorkltblSampleistItem
DBCC CHECKIDENT (tblSample, RESEED, 0)

-- now load up the tables
-- ...

David

Sorry. Type there:

PRINT 'Clearing tblSampleUnit'
DELETE FROM tblSampleUnit
DBCC CHECKIDENT (tblSampleUnit, RESEED, 0)

PRINT 'Clearing tblSample'
DELETE FROM tblSample
DBCC CHECKIDENT (tblSample, RESEED, 0)