Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

SQL Foreign Key

Foreign key is a column or field in current table and primary key of another table, the foreign key points to another table using primary key of that table.

Points to remember:

1.  Ensure value added in foreign key column exists in primary key table, otherwise a foreign key violation      error message is returned.
2.  While adding foreign key specify NOT NULL on all the participating columns, otherwise a foreign key violation error message is returned.
3.  FOREIGN KEY constraints can reference another column in the same table. This is referred to as a self-reference
4.  A column of type varchar(max) can participate in a FOREIGN KEY constraint only if the primary key it references is also defined as type varchar(max).
5.  The Database Engine does not have a predefined limit on either the number of FOREIGN KEY constraints, but we recommend using maximum 253 constraints.


Let’s explain with example:

Column Level

USE [WebShop]
GO
CREATE TABLE [dbo].[Products]
(
            [ProductID] [int] IDENTITY(1,1) NOT NULL Primary Key,
            [ProductName] [nvarchar](100) NOT NULL,
            [Description] [nvarchar](max) NOT NULL,
            [ImagePath] [nvarchar](max) NULL,
            [UnitPrice] [float] NULL,
            --Column level foreign key ,pointing to categories table
            [CategoryID] [int] NULL CONSTRAINT [FK_Products_Categories] FOREIGN KEY   REFERENCES Categories(CategoryID)
 )
GO

Table Level

USE [WebShop]
GO
CREATE TABLE [dbo].[Products]
(
            [ProductID] [int] IDENTITY(1,1) NOT NULL Primary Key,
            [ProductName] [nvarchar](100) NOT NULL,
            [Description] [nvarchar](max) NOT NULL,
            [ImagePath] [nvarchar](max) NULL,
            [UnitPrice] [float] NULL,
            [CategoryID] [int] NOT NULL,
  --Table level foreign key ,pointing to categories table
            CONSTRAINT [FK_Products_Categories] FOREIGN KEY([CategoryID]) REFERENCES
Categories(CategoryID)
 )
GO

Invalid entry,please enter valid data.

Loading