Error: no primary or candidate keys in the referenced table

Error: no primary or candidate keys in the referenced table

Post by AS » Thu, 17 Jul 2003 15:44:39



Can someone please explain me the error that I get when i run the DDl
in the query analyzer. I Created the db schema using Visio Enterprise
architect.

HServer: Msg 1776, Level 16, State 1, Line 3
There are no primary or candidate keys in the referenced table
'MerchandiseOrders' that match the referencing column list in the
foreign key 'MerchandiseOrders_MerchandiseOrdersItems_FK1'.
Server: Msg 1750, Level 16, State 1, Line 3
Could not create constraint. See previous errors.
Post a follow-up to this message

Can someone please tell me what I need to modify in Visio, prefered if
not please tell me what to change in the DDL:

go

/* Create new table "MerchandiseOrdersItems".                        
                       */
/* "MerchandiseOrdersItems" : Table of MerchandiseOrdersItems        
                       */
/*      "ProductAttributes" : ProductAttributes is of
MerchandiseOrdersItems                      */
/*      "IsKit" : IsKit is of MerchandiseOrdersItems                      
                       */
create table "MerchandiseOrdersItems" (
        "ItemID" int identity not null,
        "OrderID" int not null,
        "ProductID" int not null,
        "ProductAttributes" varchar(50) null,
        "OrderQty" int not null,
        "ItemPrice" money not null,
        "TAX" decimal(10,2) not null,
        "OrderStatus" varchar(10) not null,
        "AffiliateCode" varchar(50) not null,
        "IsKit" bit null) ON 'PRIMARY'  

go

/* Create new table "MerchandiseOrders".                              
                       */
/* "MerchandiseOrders" : Table of MerchandiseOrders                  
                       */
/*      "ShippingAddressID" : ShippingAddressID is of MerchandiseOrders  
                       */
/*      "InvoicingAddressID" : InvoicingAddressID is of MerchandiseOrders
                       */
/*      "PromotionalCode" : PromotionalCode is of MerchandiseOrders      
                       */
create table "MerchandiseOrders" (
        "OrderID" int identity not null,
        "ContactID" int not null,
        "OrderDate" datetime not null,
        "ShippingAddressID" int not null,
        "InvoicingAddressID" int not null,
        "HTMLMail" bit not null,
        "ShippingMethodID" int not null,
        "PackagingMethodID" int not null,
        "PaymentMethodID" int not null,
        "IssuerID" int null,
        "CardNumber" nvarchar(50) null,
        "ExpireDateM" ntext null,
        "ExpireDateY" ntext null,
        "NameOnCard" ntext null,
        "GiftCertificateNumber" nvarchar(50) null,
        "PromotionalCode" varchar(50) null,
        "OrderTotal" smallmoney null,
        "TotalOutstanding" smallmoney null,
        "TotalShippingCharge" smallmoney null,
        "TotalTax" smallmoney null,
        "ShipDate" datetime null,
        "Comment" text null) ON 'PRIMARY' TEXTIMAGE_ON 'PRIMARY'  

go

/* Add foreign key constraints to table "MerchandiseOrdersItems".    
                       */
alter table "MerchandiseOrdersItems"
        add constraint "MerchandiseOrders_MerchandiseOrdersItems_FK1" foreign
key (
                "OrderID")
         references "MerchandiseOrders" (
                "OrderID")  

go

alter table "MerchandiseOrdersItems"
        add constraint "Products_MerchandiseOrdersItems_FK1" foreign key (
                "ProductID")
         references "Products" (
                "ProductID")  

go

/* Add foreign key constraints to table "MerchandiseOrders".          
                       */
alter table "MerchandiseOrders"
        add constraint "PackagingMethod_MerchandiseOrders_FK1" foreign key (
                "PackagingMethodID")
         references "PackagingMethod" (
                "PackagingMethodID")  

go

alter table "MerchandiseOrders"
        add constraint "ShippingMethod_MerchandiseOrders_FK1" foreign key (
                "ShippingMethodID")
         references "ShippingMethod" (
                "ShippingMethodID")  

go

alter table "MerchandiseOrders"
        add constraint "CreditCards_MerchandiseOrders_FK1" foreign key (
                "IssuerID")
         references "CreditCards" (
                "IssuerID")

 
 
 

Error: no primary or candidate keys in the referenced table

Post by David Porta » Thu, 17 Jul 2003 15:49:26


Neither of your tables has a primary key. Add your primary keys.

--
David Portas
------------
Please reply only to the newsgroup
--


Quote:> Can someone please explain me the error that I get when i run the DDl
> in the query analyzer. I Created the db schema using Visio Enterprise
> architect.

> HServer: Msg 1776, Level 16, State 1, Line 3
> There are no primary or candidate keys in the referenced table
> 'MerchandiseOrders' that match the referencing column list in the
> foreign key 'MerchandiseOrders_MerchandiseOrdersItems_FK1'.
> Server: Msg 1750, Level 16, State 1, Line 3
> Could not create constraint. See previous errors.
> Post a follow-up to this message

> Can someone please tell me what I need to modify in Visio, prefered if
> not please tell me what to change in the DDL:

> go

> /* Create new table "MerchandiseOrdersItems".
>                        */
> /* "MerchandiseOrdersItems" : Table of MerchandiseOrdersItems
>                        */
> /* "ProductAttributes" : ProductAttributes is of
> MerchandiseOrdersItems                      */
> /* "IsKit" : IsKit is of MerchandiseOrdersItems
>                        */
> create table "MerchandiseOrdersItems" (
> "ItemID" int identity not null,
> "OrderID" int not null,
> "ProductID" int not null,
> "ProductAttributes" varchar(50) null,
> "OrderQty" int not null,
> "ItemPrice" money not null,
> "TAX" decimal(10,2) not null,
> "OrderStatus" varchar(10) not null,
> "AffiliateCode" varchar(50) not null,
> "IsKit" bit null) ON 'PRIMARY'

> go

> /* Create new table "MerchandiseOrders".
>                        */
> /* "MerchandiseOrders" : Table of MerchandiseOrders
>                        */
> /* "ShippingAddressID" : ShippingAddressID is of MerchandiseOrders
>                        */
> /* "InvoicingAddressID" : InvoicingAddressID is of MerchandiseOrders
>                        */
> /* "PromotionalCode" : PromotionalCode is of MerchandiseOrders
>                        */
> create table "MerchandiseOrders" (
> "OrderID" int identity not null,
> "ContactID" int not null,
> "OrderDate" datetime not null,
> "ShippingAddressID" int not null,
> "InvoicingAddressID" int not null,
> "HTMLMail" bit not null,
> "ShippingMethodID" int not null,
> "PackagingMethodID" int not null,
> "PaymentMethodID" int not null,
> "IssuerID" int null,
> "CardNumber" nvarchar(50) null,
> "ExpireDateM" ntext null,
> "ExpireDateY" ntext null,
> "NameOnCard" ntext null,
> "GiftCertificateNumber" nvarchar(50) null,
> "PromotionalCode" varchar(50) null,
> "OrderTotal" smallmoney null,
> "TotalOutstanding" smallmoney null,
> "TotalShippingCharge" smallmoney null,
> "TotalTax" smallmoney null,
> "ShipDate" datetime null,
> "Comment" text null) ON 'PRIMARY' TEXTIMAGE_ON 'PRIMARY'

> go

> /* Add foreign key constraints to table "MerchandiseOrdersItems".
>                        */
> alter table "MerchandiseOrdersItems"
> add constraint "MerchandiseOrders_MerchandiseOrdersItems_FK1" foreign
> key (
> "OrderID")
> references "MerchandiseOrders" (
> "OrderID")

> go

> alter table "MerchandiseOrdersItems"
> add constraint "Products_MerchandiseOrdersItems_FK1" foreign key (
> "ProductID")
> references "Products" (
> "ProductID")

> go

> /* Add foreign key constraints to table "MerchandiseOrders".
>                        */
> alter table "MerchandiseOrders"
> add constraint "PackagingMethod_MerchandiseOrders_FK1" foreign key (
> "PackagingMethodID")
> references "PackagingMethod" (
> "PackagingMethodID")

> go

> alter table "MerchandiseOrders"
> add constraint "ShippingMethod_MerchandiseOrders_FK1" foreign key (
> "ShippingMethodID")
> references "ShippingMethod" (
> "ShippingMethodID")

> go

> alter table "MerchandiseOrders"
> add constraint "CreditCards_MerchandiseOrders_FK1" foreign key (
> "IssuerID")
> references "CreditCards" (
> "IssuerID")


 
 
 

Error: no primary or candidate keys in the referenced table

Post by Aaron Bertrand - MV » Thu, 17 Jul 2003 16:40:35


You can't have a foreign key without a primary key.  You need to add primary
keys before you can add foreign keys that reference them...


Quote:> Can someone please explain me the error that I get when i run the DDl
> in the query analyzer. I Created the db schema using Visio Enterprise
> architect.

> HServer: Msg 1776, Level 16, State 1, Line 3
> There are no primary or candidate keys in the referenced table
> 'MerchandiseOrders' that match the referencing column list in the
> foreign key 'MerchandiseOrders_MerchandiseOrdersItems_FK1'.
> Server: Msg 1750, Level 16, State 1, Line 3
> Could not create constraint. See previous errors.
> Post a follow-up to this message

> Can someone please tell me what I need to modify in Visio, prefered if
> not please tell me what to change in the DDL:

> go

> /* Create new table "MerchandiseOrdersItems".
>                        */
> /* "MerchandiseOrdersItems" : Table of MerchandiseOrdersItems
>                        */
> /* "ProductAttributes" : ProductAttributes is of
> MerchandiseOrdersItems                      */
> /* "IsKit" : IsKit is of MerchandiseOrdersItems
>                        */
> create table "MerchandiseOrdersItems" (
> "ItemID" int identity not null,
> "OrderID" int not null,
> "ProductID" int not null,
> "ProductAttributes" varchar(50) null,
> "OrderQty" int not null,
> "ItemPrice" money not null,
> "TAX" decimal(10,2) not null,
> "OrderStatus" varchar(10) not null,
> "AffiliateCode" varchar(50) not null,
> "IsKit" bit null) ON 'PRIMARY'

> go

> /* Create new table "MerchandiseOrders".
>                        */
> /* "MerchandiseOrders" : Table of MerchandiseOrders
>                        */
> /* "ShippingAddressID" : ShippingAddressID is of MerchandiseOrders
>                        */
> /* "InvoicingAddressID" : InvoicingAddressID is of MerchandiseOrders
>                        */
> /* "PromotionalCode" : PromotionalCode is of MerchandiseOrders
>                        */
> create table "MerchandiseOrders" (
> "OrderID" int identity not null,
> "ContactID" int not null,
> "OrderDate" datetime not null,
> "ShippingAddressID" int not null,
> "InvoicingAddressID" int not null,
> "HTMLMail" bit not null,
> "ShippingMethodID" int not null,
> "PackagingMethodID" int not null,
> "PaymentMethodID" int not null,
> "IssuerID" int null,
> "CardNumber" nvarchar(50) null,
> "ExpireDateM" ntext null,
> "ExpireDateY" ntext null,
> "NameOnCard" ntext null,
> "GiftCertificateNumber" nvarchar(50) null,
> "PromotionalCode" varchar(50) null,
> "OrderTotal" smallmoney null,
> "TotalOutstanding" smallmoney null,
> "TotalShippingCharge" smallmoney null,
> "TotalTax" smallmoney null,
> "ShipDate" datetime null,
> "Comment" text null) ON 'PRIMARY' TEXTIMAGE_ON 'PRIMARY'

> go

> /* Add foreign key constraints to table "MerchandiseOrdersItems".
>                        */
> alter table "MerchandiseOrdersItems"
> add constraint "MerchandiseOrders_MerchandiseOrdersItems_FK1" foreign
> key (
> "OrderID")
> references "MerchandiseOrders" (
> "OrderID")

> go

> alter table "MerchandiseOrdersItems"
> add constraint "Products_MerchandiseOrdersItems_FK1" foreign key (
> "ProductID")
> references "Products" (
> "ProductID")

> go

> /* Add foreign key constraints to table "MerchandiseOrders".
>                        */
> alter table "MerchandiseOrders"
> add constraint "PackagingMethod_MerchandiseOrders_FK1" foreign key (
> "PackagingMethodID")
> references "PackagingMethod" (
> "PackagingMethodID")

> go

> alter table "MerchandiseOrders"
> add constraint "ShippingMethod_MerchandiseOrders_FK1" foreign key (
> "ShippingMethodID")
> references "ShippingMethod" (
> "ShippingMethodID")

> go

> alter table "MerchandiseOrders"
> add constraint "CreditCards_MerchandiseOrders_FK1" foreign key (
> "IssuerID")
> references "CreditCards" (
> "IssuerID")