hi there!
im having problems deleting rows in a reference table. is there any tools which tables to delete first before deleting the rows in the table which contains the primary key?
i have a lot of tables let say over 300 so its hard for me to guess which comes first... what should i keep in mind deleting rows with a referential integrity?
thank...
1. You can use sys.foreign_keys view to query and follow the data constraints in your table2. You may try to use cascading referential integrity constraints.
By using cascading referential integrity constraints, you can define the
actions that the SQL Server 2005 takes when a user tries to delete or update a
key to which existing foreign keys point.
The REFERENCES clauses of the CREATE TABLE and
ALTER
TABLE statements support the ON DELETE and ON UPDATE clauses:
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
]
|||
hi carlop!
thank you for reply.... well the table in our database are not set to ON DELETE CASCADE ON due to security reason. so there is no way for me to delete the rows easily, i guess i should track all the tables for their foreign keys and dependent tables :-(
thanks, novelle
|||hi there...
do u want to delete data from ur selected tables, and dont want their parent tables(PK tables) , to give foreign key errors......if thats the case, u can disable the foreign keys..perform the operation, then enable them again..
else if u want to delete from primary table first...or want to know the related tables, either use database digrams...or maybe this query will help u..
select a.name,c.name as pk_table ,b.name fk_table
from sys.foreign_keys a
inner join sys.sysobjects b on b.id = a.parent_object_id
inner join sys.sysobjects c on c.id = a.referenced_object_id
|||
hi nitin!
thank u for ur quick reply!
the thing is, im copying data from server to server, after copying the data i wanted to deleted this rows i've copied to the source database and offcourse i want to delete correctly.
i dont want to disable the foreignkeys because if my delete script is wrong , i wont able to delete the data correctly.
by the way does this script works on the SQL 2000? because i've tried it and it doesnt work.
thanks novelle.
|||hi...for 2000 it'll be like
select a.name,c.name as pk_table ,b.name fk_table
from sys.foreign_keys a -- for this pls check the table sysconstraints/sysreferences...i dont quite remember the fields..
inner join sysobjects b on b.id = a.parent_object_id
inner join sysobjects c on c.id = a.referenced_object_id
|||A sql 2k compliant view of foreign keys and primary keys is the following:CREATE view dbo.foreign_keys as
select cast(f.name as varchar(255)) as fk_name
, r.keycnt
, cast(ft.name as varchar(255)) as foreign_table
, cast(f1.name as varchar(255)) as foreign_col1
, cast(f2.name as varchar(255)) as foreign_col2
, cast(pt.name as varchar(255)) as primary_table
, cast(p1.name as varchar(255)) as primary_col1
, cast(p2.name as varchar(255)) as primary_col2
from sysobjects f
inner join sysobjects ft on f.parent_obj = ft.id
inner join sysreferences r on f.id = r.constid
inner join sysobjects pt on r.rkeyid = pt.id
inner join syscolumns p1 on r.rkeyid = p1.id and r.rkey1 = p1.colid
inner join syscolumns f1 on r.fkeyid = f1.id and r.fkey1 = f1.colid
left join syscolumns p2 on r.rkeyid = p2.id and r.rkey2 = p1.colid
left join syscolumns f2 on r.fkeyid = f2.id and r.fkey2 = f1.colid
where f.type = 'F'
GO
CREATE view dbo.primary_keys as
select distinct
tbl.name TableName,
constrId.name PkName,
col.name ColName,
sik.keyno,
case when ix.indid = 1 then 1 else 0 end IsClustered
from sysobjects tbl
join sysconstraints constr on ( tbl.id = constr.id and tbl.xtype = 'U' and constr.status & 0x0001 = 0x0001 )
join sysobjects constrId on constrId.parent_obj = tbl.id and constrId.xtype = 'PK'
join sysindexes ix on constrId.name = ix.name and ix.id = tbl.id
join sysindexkeys sik on sik.id = tbl.id and sik.indid = ix.indid
join syscolumns col on col.id = tbl.id and sik.colid = col.colid
GO
