Wednesday, March 7, 2012

creating a FK on existing tables

hello
I am working with an existing database and there is no Foreign key between 2 tables
how can i create a FK after , when the tables are allready full ?

product :

product_id
report_id
name

report :

report_id
dateR

i want to create a FK on product.report_id, and ON DELETE CASCADE

thank you--Creating table with same structure (Primary key)]
CREATE TABLE [A] (
[report_id] [varchar] (10) ,
[dateR] [Datetime],
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[report_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
--Inserting data from report table
INSERT INTO A
SELECT * FROM report

--Dropping table report
DROP TABLE report
GO
--Renaming A table as report table
EXEC sp_rename 'A','report'
GO
--Caution: Changing any part of an object name
--could break scripts and stored procedures.

--This will create a FK in product table

ALTER TABLE products WITH NOCHECK
ADD CONSTRAINT exd_check FOREIGN KEY
(
[report_id]
) REFERENCES [report] (
[report_id]
) ON DELETE CASCADE|||genial !

thanks a lot

No comments:

Post a Comment