10777 - difference REFERENCES and FOREING KEY

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
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

There is no difference between reference and Foriegn key it is just syntax difference and the readability which makes them separate...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.