Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Posts
    6

    Unanswered: Create Table with Foreign Keys

    How do I create a table with a foreign key

  2. #2
    Join Date
    May 2007
    Posts
    4
    CREATE TABLE Parent ( ParentId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ColA INT)

    CREATE TABLE Child(ChildId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ParentId INT NOT NULL CONSTRAINT FK_Child_Parent1 FOREIGN KEY REFERENCES Parent(ParentId))

  3. #3
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    also a foreign key has the following options

    FOREIGN KEY
    [ ( column [ ,...n ] ) ]
    REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
    [ ON DELETE { CASCADE | NO ACTION } ]
    [ ON UPDATE { CASCADE | NO ACTION } ]

    FOREIGN KEY...REFERENCES

    Is a constraint that provides referential integrity for the data in the column or columns. FOREIGN KEY constraints require that each value in the column exists in the corresponding referenced column(s) in the referenced table. FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on the referenced table.

    ref_table

    Is the name of the table referenced by the FOREIGN KEY constraint.

    (ref_column[,...n])

    Is a column, or list of columns, from the table referenced by the FOREIGN KEY constraint.

    ON DELETE {CASCADE | NO ACTION}

    Specifies what action takes place to a row in the table created, if that row has a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

    If CASCADE is specified, a row is deleted from the referencing table if that row is deleted from the parent table. If NO ACTION is specified, SQL Server raises an error and the delete action on the row in the parent table is rolled back.

    For example, in the Northwind database, the Orders table has a referential relationship with the Customers table. The Orders.CustomerID foreign key references the Customers.CustomerID primary key.

    If a DELETE statement is executed on a row in the Customers table, and an ON DELETE CASCADE action is specified for Orders.CustomerID, SQL Server checks for one or more dependent rows in the Orders table. If any, the dependent rows in the Orders table are deleted, as well as the row referenced in the Customers table.

    On the other hand, if NO ACTION is specified, SQL Server raises an error and rolls back the delete action on the Customers row if there is at least one row in the Orders table that references it.

    ON UPDATE {CASCADE | NO ACTION}

    Specifies what action takes place to a row in the table created, if that row has a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.

    If CASCADE is specified, the row is updated in the referencing table if that row is updated in the parent table. If NO ACTION is specified, SQL Server raises an error and the update action on the row in the parent table is rolled back.

    For example, in the Northwind database, the Orders table has a referential relationship with the Customers table: Orders.CustomerID foreign key references the Customers.CustomerID primary key.

    If an UPDATE statement is executed on a row in the Customers table, and an ON UPDATE CASCADE action is specified for Orders.CustomerID, SQL Server checks for one or more dependent rows in the Orders table. If any exist, the dependent rows in the Orders table are updated, as well as the row referenced in the Customers.

    Alternately, if NO ACTION is specified, SQL Server raises an error and rolls back the update action on the Customers row if there is at least one row in the Orders table that references it.

    Also you should know
    FOREIGN KEY Constraints
    When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned.


    FOREIGN KEY constraints are applied to the preceding column unless source columns are specified.


    FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers. For more information, see CREATE TRIGGER.


    FOREIGN KEY constraints can reference another column in the same table (a self-reference).


    The REFERENCES clause of a column-level FOREIGN KEY constraint can list only one reference column, which must have the same data type as the column on which the constraint is defined.


    The REFERENCES clause of a table-level FOREIGN KEY constraint must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.


    CASCADE may not be specified if a column of type timestamp is part of either the foreign key or the referenced key.


    It is possible to combine CASCADE and NO ACTION on tables that have referential relationships with each other. If SQL Server encounters NO ACTION, it terminates and rolls back related CASCADE actions. When a DELETE statement causes a combination of CASCADE and NO ACTION actions, all the CASCADE actions are applied before SQL Server checks for any NO ACTION.


    A table can contain a maximum of 253 FOREIGN KEY constraints.


    FOREIGN KEY constraints are not enforced on temporary tables.


    A table can reference a maximum of 253 different tables in its FOREIGN KEY constraints.


    FOREIGN KEY constraints can reference only columns in PRIMARY KEY or UNIQUE constraints in the referenced table or in a UNIQUE INDEX on the referenced table.
    Last edited by nick.ncs; 05-30-07 at 07:41.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by nick.ncs
    also a foreign key has the following options

    FOREIGN KEY
    [ ( column [ ,...n ] ) ]
    REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
    [ ON DELETE { CASCADE | NO ACTION } ]
    [ ON UPDATE { CASCADE | NO ACTION } ]
    .............
    If you are using SQL Server 2005 you would do well to read the BoL entry as there are now more options than those above - more in line with the sql standard.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Hopefully Nick won't be copying & Pasting the BOL entrys into this thread as well - lol

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am finishing up some handy dadndy little sql to find all of the foriegn keys without an index.

    I need to find something better to do with my time.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    Quote Originally Posted by GWilliy
    Hopefully Nick won't be copying & Pasting the BOL entrys into this thread as well - lol

    GW
    no i wont.... i guess the previous one was enough

Posting Permissions

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