Results 1 to 5 of 5

Thread: Foreign Keys!

  1. #1
    Join Date
    Sep 2003
    Location
    St.Petersburg, Russia
    Posts
    19

    Question Unanswered: Foreign Keys!

    Hey!

    I'm wondering if MS SQL supports flexible foreign keys, I mean if it supports SET NULL, CASCADE, etc.

    I hvae the following code:
    Code:
    CREATE TABLE MyOrgs
    (
      id INT NOT NULL,
      name VARCHAR ( 30 ),
      PRIMARY KEY ( id )
    )
    CREATE TABLE MyUsers
    (
      id INT NOT NULL,
      name VARCHAR ( 30 ),
      org INT,
      PRIMARY KEY ( id ),
      FOREIGN KEY ( org )
      REFERENCES MyOrgs
      ON DELETE SET NULL 
    )
    But it gives this error 'Server: Msg 156, Level 15, State 1, Line 15
    Incorrect syntax near the keyword 'ON'.'.

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Foreign Keys!

    Which version do you use? SQL Server 7 does not support the specification of cascading deletes or updates on foreign keys.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Sep 2003
    Posts
    4

    Re: Foreign Keys!

    Originally posted by Mike Borozdin
    Hey!

    I'm wondering if MS SQL supports flexible foreign keys, I mean if it supports SET NULL, CASCADE, etc.

    I hvae the following code:
    Code:
    CREATE TABLE MyOrgs
    (
      id INT NOT NULL,
      name VARCHAR ( 30 ),
      PRIMARY KEY ( id )
    )
    CREATE TABLE MyUsers
    (
      id INT NOT NULL,
      name VARCHAR ( 30 ),
      org INT,
      PRIMARY KEY ( id ),
      FOREIGN KEY ( org )
      REFERENCES MyOrgs
      ON DELETE SET NULL 
    )
    But it gives this error 'Server: Msg 156, Level 15, State 1, Line 15
    Incorrect syntax near the keyword 'ON'.'.
    Hello,

    Transact SQL does allow nullable foreign key columns. However it does not support cascading deletes. You must explicitly set the foreign key column to null before deleting a record from the parent table - MyOrgs.

  4. #4
    Join Date
    Sep 2003
    Posts
    4
    Hello again,
    SQL Server 2000 (and 7?) does support cascading updates and deletes, but UPDATE CASCADE will set the FK to the new value of the PK (which cannot be null), and DELETE CASCADE will delete the dependent table record instead of setting its FK column to null
    ex. of cascading delete:

    CREATE TABLE MyOrgs
    (
    id INT NOT NULL,
    name VARCHAR ( 30 ),
    PRIMARY KEY ( id )
    )
    CREATE TABLE MyUsers
    (
    id INT NOT NULL,
    name VARCHAR ( 30 ),
    org INT,
    PRIMARY KEY ( id ),
    FOREIGN KEY ( org )
    REFERENCES MyOrgs
    ON UPDATE CASCADE
    )
    GO

    insert into MyOrgs
    values (1, 'MyOrgsRec')
    GO

    insert into MyUsers
    values (2, 'MyUsersRec', 1)

    select * from MyOrgs
    select * from MyUsers

    -- Deleting a record from MyOrgs will also delete records in MyUsers
    -- where the org column = MyOrgs PK column.
    PRINT 'Deleting record in MyOrgs'
    DELETE MyOrgs
    GO

    select * from MyOrgs
    select * from MyUsers
    GO

  5. #5
    Join Date
    Dec 2003
    Posts
    1
    To obtain the effect on ON DELETE SET NULL, try creating a INSTEAD OF DELETE trigger on MyOrgs - You could try something like this:

    CREATE TABLE MyOrgs
    (
    id INT NOT NULL,
    name VARCHAR ( 30 ),
    PRIMARY KEY ( id )
    )
    go
    CREATE TABLE MyUsers
    (
    id INT NOT NULL,
    name VARCHAR ( 30 ),
    org INT,
    PRIMARY KEY ( id ),
    FOREIGN KEY ( org )
    REFERENCES MyOrgs
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    )
    GO

    CREATE TRIGGER Test2 ON MyOrgs
    INSTEAD OF DELETE
    AS
    DECLARE @PrimaryKey integer
    SELECT @PrimaryKey = id FROM deleted

    if @PrimaryKey is not null
    Begin
    update MyUsers set org = null where org = @PrimaryKey
    delete from MyOrgs where id = @PrimaryKey
    end

    insert into MyOrgs
    values (1, 'MyOrgsRec')
    GO

    insert into MyUsers
    values (2, 'MyUsersRec', 1)

    select * from MyOrgs
    select * from MyUsers

    PRINT 'Deleting record in MyOrgs'
    DELETE MyOrgs
    GO

    select * from MyOrgs
    select * from MyUsers
    GO


    Hope this helps

Posting Permissions

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