Solved

10777 - difference REFERENCES and FOREING KEY

Posted on 2014-07-18
2
325 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 32

Accepted Solution

by:
ste5an earned 200 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 14

Assisted Solution

by:Vikas Garg
Vikas Garg earned 50 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now