Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: 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

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Code:
    --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
    Last edited by rudra; 09-22-06 at 11:26.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    genial !

    thanks a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •