May 30, 2011 at 10:13 am
In SSMS, I click a constraint and generate script create to, I got something like below:
USE [Asset5]
GO
ALTER TABLE [dbo].[Inventory] WITH CHECK ADD CONSTRAINT [FK_Inventory_Lease] FOREIGN KEY([LeaseId])
REFERENCES [dbo].[Lease] ([LeaseId])
GO
ALTER TABLE [dbo].[Inventory] CHECK CONSTRAINT [FK_Inventory_Lease]
GO
In above statement, what does check add mean, and what does check constraint mean?
I thought it could be simple as : (Sorry to use another table for example)
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
My second question about this: if I have a lot of foreign key needs to be dropped and added back,
is there a way to generate a list of add contraints script instead of typing one by one?
Thanks
May 30, 2011 at 10:31 am
The first check means add this fk and enable it (you could add it but disabled using nocheck).
The 2nd alter means to go in and validate all the data right now.
May 31, 2011 at 2:38 am
--Drop all FKs in the database for the selecting tables
DECLARE
@table_name sysname,
@constraint_name sysname
DECLARE i CURSOR STATIC
FOR
SELECT
c.table_name,
a.constraint_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b ON a.unique_constraint_name = b.constraint_name
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c ON a.constraint_name = c.constraint_name
WHERE UPPER(c.table_name) IN (UPPER('Lease'), UPPER('Inventory'))
May 31, 2011 at 2:39 am
oops.. second part of previos script
OPEN i
FETCH NEXT FROM i INTO @table_name, @constraint_name
WHILE @@fetch_status=0
BEGIN
--EXEC('ALTER TABLE '+@table_name+' DROP CONSTRAINT '+@constraint_name)
PRINT 'ALTER TABLE '+@table_name+' DROP CONSTRAINT '+@constraint_name
FETCH NEXT FROM i INTO @table_name,@constraint_name
END
CLOSE i
DEALLOCATE i
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply