Solved

10777 - difference REFERENCES and FOREING KEY

Posted on 2014-07-18
2
326 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 33

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

919 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

24 Experts available now in Live!

Get 1:1 Help Now