?
Solved

10777 - difference REFERENCES and FOREING KEY

Posted on 2014-07-18
2
Medium Priority
?
339 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 35

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 real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

770 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