Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

10777 - difference REFERENCES and FOREING KEY

Posted on 2014-07-18
2
Medium Priority
?
341 Views
Last Modified: 2014-07-22
i have this create table

  -- CREATE A FACT TABLE
  CREATE TABLE FactSalesOrders
  (ProductKey int NOT NULL REFERENCES DimProduct(ProductKey),
   CustomerKey int NOT NULL REFERENCES DimCustomer(CustomerKey),
   SalespersonKey int NOT NULL REFERENCES DimSalesperson(SalespersonKey),
   OrderDateKey int NOT NULL REFERENCES DimDate(DateKey),
   OrderNo int NOT NULL,
   ItemNo int NOT NULL,
   Quantity int NOT NULL,
   SalesAmount money NOT NULL,
   Cost money NOT NULL
    CONSTRAINT [PK_ FactSalesOrder] PRIMARY KEY NONCLUSTERED
 (
      [ProductKey],[CustomerKey],[SalesPersonKey],[OrderDateKey],[OrderNo],[ItemNo]
 )
 )
GO

what it is difference beetwen REFERENCES and FOREING KEY
0
Comment
Question by:enrique_aeo
2 Comments
 
LVL 36

Accepted Solution

by:
ste5an earned 800 total points
ID: 40204260
Well, they are different key words..

But the thing you need to know: it's the short-hand inline declaration of a foreign key.
<column_constraint> ::= 
[ CONSTRAINT constraint_name ] 
{     { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
        [ 
            WITH FILLFACTOR = fillfactor  
          | WITH ( < index_option > [ , ...n ] ) 
        ] 
        [ ON { partition_scheme_name ( partition_column_name ) 
            | filegroup | "default" } ]
  | [ FOREIGN KEY ] 
        REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
  | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 
} 

Open in new window


p.s. imho you should always name your constraints explictly. Either inline
CREATE TABLE FactSalesOrders
    (
      ProductKey INT NOT NULL
                     CONSTRAINT FK_FactSalesOrders_ProductKey FOREIGN KEY REFERENCES DimProduct ( ProductKey ) ,
      CustomerKey INT NOT NULL
                      CONSTRAINT FK_FactSalesOrders_CustomerKey FOREIGN KEY REFERENCES DimCustomer ( CustomerKey ) ,
      SalespersonKey INT NOT NULL
                         CONSTRAINT FK_FactSalesOrders_SalespersonKey FOREIGN KEY REFERENCES DimSalesperson ( SalespersonKey ) ,
      OrderDateKey INT NOT NULL
                       CONSTRAINT FK_FactSalesOrders_OrderDateKey FOREIGN KEY REFERENCES DimDate ( DateKey ) ,
      OrderNo INT NOT NULL ,
      ItemNo INT NOT NULL ,
      Quantity INT NOT NULL ,
      SalesAmount MONEY NOT NULL ,
      Cost MONEY NOT NULL CONSTRAINT PK_FactSalesOrders PRIMARY KEY NONCLUSTERED ( ProductKey, CustomerKey, SalesPersonKey, OrderDateKey, OrderNo, ItemNo )
    );

Open in new window

or afterwards
CREATE TABLE FactSalesOrders
    (
      ProductKey INT NOT NULL ,
      CustomerKey INT NOT NULL ,
      SalespersonKey INT NOT NULL ,
      OrderDateKey INT NOT NULL ,
      OrderNo INT NOT NULL ,
      ItemNo INT NOT NULL ,
      Quantity INT NOT NULL ,
      SalesAmount MONEY NOT NULL ,
      Cost MONEY NOT NULL ,
      CONSTRAINT PK_FactSalesOrders PRIMARY KEY NONCLUSTERED ( ProductKey, CustomerKey, SalesPersonKey, OrderDateKey, OrderNo, ItemNo ) ,
      CONSTRAINT FK_FactSalesOrders_ProductKey FOREIGN KEY ( ProductKey ) REFERENCES DimProduct ( ProductKey ) ,
      CONSTRAINT FK_FactSalesOrders_CustomerKey FOREIGN KEY ( CustomerKey ) REFERENCES DimCustomer ( CustomerKey ) ,
      CONSTRAINT FK_FactSalesOrders_SalespersonKey FOREIGN KEY ( SalespersonKey ) REFERENCES DimSalesperson ( SalespersonKey ) ,
      CONSTRAINT FK_FactSalesOrders_OrderDateKey FOREIGN KEY ( OrderDateKey ) REFERENCES DimDate ( DateKey )
    );

Open in new window

0
 
LVL 15

Assisted Solution

by:Vikas Garg
Vikas Garg earned 200 total points
ID: 40204339
Hi,

There is no difference between reference and Foriegn key it is just syntax difference and the readability which makes them separate...
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question