DDL Errors: Msgs 1913/Level 16 and 1776/Level 16

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DDL Errors: Msgs 1913/Level 16 and 1776/Level 16

Author

Topic

lueylump
Starting Member

25 Posts

Posted - 2010-04-27 : 13:20:01

I am maintaining a database via diagrams that were created in Visio for MS Visual Studio. I am trying to creating unique alternate indexes on columns other than the primary key. Visio creates the DDL in multiple steps (see below) so it alters a table to add a Unique Constraint, and then creates a Unique Index using the same (foreign key) name. At that point I get the 1913. FYI I also reversed the sequence where it Creates the index first and then applies the Unique constraint, but that also did not work.

The final step is to relate the unique alternate index with the Foreign Key of another table by actually referencing the column names, and that is when I get the 1776.

Any assistance I receive on this matter is greatly appreciated as I am pulling my hair out on this one (see DDL Below with actual error messages embedded in the offending code).

create table "Contact" (
"Cntct_ID" int not null,
"Org_ID" int null,
"Loc_ID" int not null,
"Cntct_Eff_Dt" datetime null,
"Cntct_Trmn_Dt" datetime null,
"Cntct_Title" varchar(10) null,
"Cntct_Fir_Nm" varchar(20) null,
"Cntct_Mid_Nm" varchar(20) null,
"Cntct_Lst_Nm" varchar(30) null,
"Cntct_Sfx" varchar(10) null,
"Cntct_Email_Addr" varchar(60) null,
"Cntct_Position" varchar(30) null,
"Cntct_Spouse_Nm" varchar(50) null,
"Cntct_Birth_Dt" datetime null,
"Cntct_Anniv_Dt" datetime null,
"Cntct_Note" text null)

go

alter table "Contact"
add constraint "Contact_PK" primary key ("Cntct_ID")

create table "Cust_Shipping" (
"Cust_Ord_ID" int not null,
"Cust_Ship_Loc_Nbr" int not null,
"Ship_to_Org_ID" int null,
"Ship_to_Loc_ID" int null,
"Ship_to_Cntct_ID" int null,
"Cust_Ship_Alt_Addr_Fl" bit null,
"Cust_Ship_Nm" varchar(50) null,
"Cust_Ship_Addr_Ln_1" varchar(60) null,
"Cust_Ship_Addr_Ln_2" varchar(60) null,
"Cust_Ship_City" varchar(20) null,
"Cust_Ship_St" char(2) null,
"Cust_Ship_Zip" char(10) null,
"Cust_Ship_Country" varchar(60) null,
"Cust_Ship_Attn" varchar(50) null,
"Shipper_Org_ID" int null)

go

alter table "Cust_Shipping"
add constraint "Customer_Order_Shipping_PK" primary key ("Cust_Ord_ID", "Cust_Ship_Loc_Nbr")

go

create table "Organization" (
"Org_ID" int not null,
"Org_Nm" varchar(50) null,
"Org_Typ_Cd" char(1) null,
"Org_Stat_Cd" char(1) null,
"Org_Wbst_URL" varchar(60) null,
"Org_HP_Donor_Fl" bit null)

go

alter table "Organization"
add constraint "Organization_PK" primary key ("Org_ID")

go

create table "Cust_Order_Item" (
"Cust_Ord_ID" int not null,
"Cust_Ord_LI_Nbr" int not null,
"Cust_Ship_Loc_Nbr" int null,
"Cust_Ord_LI_Dlvry_Dt" datetime null,
"Prod_ID" varchar(20) null,
"Cust_Prod_Nbr" varchar(20) null,
"Cust_Prod_Desc" varchar(200) null,
"Cust_Ord_LI_Qty" int null,
"Cust_Ord_LI_UM" char(2) null,
"Cust_Ord_LI_Unit_Price" decimal(12,4) null,
"Cust_Ord_LI_Total" smallmoney null,
"Cust_Ord_LI_Need_Dt" datetime null,
"Cust_Ord_LI_Orig_Ord_Nbr" varchar(20) null,
"Cust_Ord_LI_Inv_Cmttd_Fl" bit null,
"Cust_Ord_LI_Stat_Cd" char(1) null,
"Cust_Ord_LI_Flfld_Qty" int null,
"Cust_Ord_LI_Shpd_Qty" int null,
"Cust_Ord_LI_Wgt" decimal(12,4) null)

go

alter table "Cust_Order_Item"
add constraint "Customer_Order_Item_PK" primary key ("Cust_Ord_ID", "Cust_Ord_LI_Nbr")

go

create table "Location" (
"Loc_ID" int not null,
"Org_ID" int not null,
"Loc_Eff_Dt" datetime null,
"Loc_Trmn_Dt" datetime null,
"Loc_Nm" varchar(60) null,
"Loc_Addr_Ln_1" varchar(60) null,
"Loc_Addr_Ln_2" varchar(60) null,
"Loc_City" varchar(60) null,
"Loc_State" char(2) null,
"Loc_Zip" char(10) null,
"Country_Cd" char(3) null,
"Org_Typ_Cd" char(1) null)

go

alter table "Location"
add constraint "Location_PK" primary key ("Loc_ID")

go

create table "Cust_Order" (
"Cust_Ord_ID" int not null,
"Org_ID" int null,
"Buyer_Loc_ID" int null,
"Buyer_Cntct_ID" int null,
"Cust_Ord_Ntrnl_Fl" bit null,
"Cust_Ord_Intl_Fl" bit null,
"Cust_Ord_PO_Nbr" varchar(20) null,
"Cust_Ord_PO_Rev_Nbr" varchar(20) null,
"Ord_Stat_Cd" char(1) null,
"Cust_Ord_Dt" datetime null,
"Cust_Ord_Need_Dt" datetime null,
"Cust_Ord_Disp_Dt" datetime null,
"Cust_Ord_Total_Amt" smallmoney null,
"Cust_Ord_Disc_Prcnt" decimal(10,2) null,
"Elim_Invoice_Nbr" varchar(20) null,
"Bill_to_Org_ID" int not null,
"Bill_to_Loc_ID" int not null,
"Cust_Ord_Pay_Trms" varchar(15) null,
"Cust_Ord_FOB" varchar(15) null,
"Cust_Ord_Freight_Trms" varchar(15) null,
"Cust_Ord_Tax_Ex_Fl" bit null,
"Cust_Ord_Ship_Via" varchar(20) null,
"Cust_Ord_Conf_Fl" bit null,
"Cust_Ord_Note" text null,
"Cust_Ord_Rcvr" varchar(30) null,
"Cntct_ID" int null)

go

alter table "Cust_Order"
add constraint "Customer_Order_PK" primary key ("Cust_Ord_ID")

go

alter table "Contact" add constraint "Org_Loc_Cntct_FK1" unique (
"Org_ID",
"Loc_ID",
"Cntct_ID")

go
/*
create unique index "Org_Loc_Cntct_FK1" on "Contact" (
"Org_ID",
"Loc_ID",
"Cntct_ID")

Msg 1913, Level 16, State 1, Line 196
The operation failed because an index or statistics with name
'Org_Loc_Cntct_FK1' already exists on table 'Contact'.

go
*/
create index "Cust_Ord_Cust_Ord_Item_FK1" on "Cust_Order_Item" (
"Cust_Ord_ID")

go

create unique index "Org_Loc_FK1" on "Location" (
"Org_ID",
"Loc_ID")

go
/*
alter table "Location" add constraint "Org_Loc_FK1" unique (
"Org_ID",
"Loc_ID")

Msg 1913, Level 16, State 1, Line 221
The operation failed because an index or statistics with name '
Org_Loc_FK1' already exists on table 'Location'.
Msg 1750, Level 16, State 0, Line 221
Could not create constraint. See previous errors.
go
*/
create index "Cust_Cust_Order_FK1" on "Cust_Order" (
"Org_ID")

go

create index "Loc_Cust_Ord_FK1" on "Cust_Order" (
"Bill_to_Org_ID",
"Bill_to_Loc_ID")
/*
Msg 102, Level 15, State 1, Line 238
Incorrect syntax near '('.
*/

go

create index "Contact_Cust_Ord_FK1" on "Cust_Order" (
"Org_ID",
"Buyer_Loc_ID",
"Buyer_Cntct_ID")

go

alter table "Contact"
add constraint "Location_Contact_FK1" foreign key (
"Loc_ID",
"Org_ID")
references "Location" (
"Loc_ID",
"Org_ID")
/*
Msg 1776, Level 16, State 0, Line 256
There are no primary or candidate keys in the referenced table 'Location'
that match the referencing column list in the foreign key 'Location_Contact_FK1'.
Msg 1750, Level 16, State 0, Line 256
Could not create constraint. See previous errors.*/
go

alter table "Contact"
add constraint "Organization_Contact_FK1" foreign key (
"Org_ID")
references "Organization" (
"Org_ID")

go

alter table "Cust_Shipping"
add constraint "Customer_Order_Customer_Order_Shipping_FK1" foreign key (
"Cust_Ord_ID")
references "Cust_Order" (
"Cust_Ord_ID")

go

alter table "Cust_Shipping"
add constraint "Contact_Cust_Shipping_FK1" foreign key (
"Ship_to_Cntct_ID",
"Ship_to_Org_ID",
"Ship_to_Loc_ID")
references "Contact" (
"Cntct_ID",
"Org_ID",
"Loc_ID")
/*
Msg 1776, Level 16, State 0, Line 290
There are no primary or candidate keys in the referenced table 'Contact' that
match the referencing column list in the foreign key 'Contact_Cust_Shipping_FK1'.
Msg 1750, Level 16, State 0, Line 290
Could not create constraint. See previous errors.*/
go

alter table "Cust_Order_Item"
add constraint "Customer_Order_Customer_Order_Item_FK1" foreign key (
"Cust_Ord_ID")
references "Cust_Order" (
"Cust_Ord_ID")

go

alter table "Cust_Order_Item"
add constraint "Cust_Shipping_Cust_Order_Item_FK1" foreign key (
"Cust_Ord_ID",
"Cust_Ship_Loc_Nbr")
references "Cust_Shipping" (
"Cust_Ord_ID",
"Cust_Ship_Loc_Nbr")

go

alter table "Location"
add constraint "Organization_Location_FK1" foreign key (
"Org_ID")
references "Organization" (
"Org_ID")

go

alter table "Cust_Order"
add constraint "Location_Cust_Order_FK1" foreign key (
"Bill_to_Loc_ID",
"Bill_to_Org_ID")
references "Location" (
"Loc_ID",
"Org_ID")
/*
Msg 1776, Level 16, State 0, Line 334
There are no primary or candidate keys in the referenced table 'Location' that
match the referencing column list in the foreign key 'Location_Cust_Order_FK1'.
Msg 1750, Level 16, State 0, Line 334
Could not create constraint. See previous errors.
*/go

alter table "Cust_Order"
add constraint "Contact_Cust_Order_FK1" foreign key (
"Buyer_Cntct_ID",
"Org_ID",
"Buyer_Loc_ID")
references "Contact" (
"Cntct_ID",
"Org_ID",
"Loc_ID")

go

Msg 1776, Level 16, State 0, Line 350
There are no primary or candidate keys in the referenced table 'Contact' that
match the referencing column list in the foreign key 'Contact_Cust_Order_FK1'.
Msg 1750, Level 16, State 0, Line 350
Could not create constraint. See previous errors.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 13:33:11

quote:
alter table "Contact" add constraint "Org_Loc_Cntct_FK1" unique (
"Org_ID",
"Loc_ID",
"Cntct_ID")

go
/*
create unique index "Org_Loc_Cntct_FK1" on "Contact" (
"Org_ID",
"Loc_ID",
"Cntct_ID")

You don't need both of these. You can remove one.

The problem is because you're using the same name for each of them, "Org_Loc_Cntct_FK1". WHy Visio did this is anyone's guess.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-27 : 13:46:48

Visio should not be creating an index for the unique constraint. SQL Server automatically does that for us, so it should not be in your script. Remove those lines from your script rather than changing the index name.

Here's the info from BOL:

quote:

When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lueylump
Starting Member

25 Posts

Posted - 2010-04-27 : 14:01:14

Thanks for your comments! I eliminated one of the redundant instructions (in one case I eliminated the constraint and in another case I eliminated the unique index), and that solved the 1913 problem, but I am still getting the 1776s because it is not associating the foreign key columns with the referencing columns. Any other ideas?

Msg 1776, Level 16, State 0, Line 2
There are no primary or candidate keys in the referenced table 'Location' that match the referencing column list in the foreign key 'Location_Contact_FK1'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

Msg 1776, Level 16, State 0, Line 2
There are no primary or candidate keys in the referenced table 'Contact' that match the referencing column list in the foreign key 'Contact_Cust_Shipping_FK1'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

Msg 1776, Level 16, State 0, Line 2
There are no primary or candidate keys in the referenced table 'Location' that match the referencing column list in the foreign key 'Location_Cust_Order_FK1'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

Msg 1776, Level 16, State 0, Line 2
There are no primary or candidate keys in the referenced table 'Contact' that match the referencing column list in the foreign key 'Contact_Cust_Order_FK1'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-27 : 14:05:05

You can't put a foreign key on column(s) unless a PK or unique constraint exists, so that needs to be corrected in your script. Look at the foreign keys that are erroring and then add code before that statement to add the PK or unique constraint.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lueylump
Starting Member

25 Posts

Posted - 2010-04-27 : 14:51:58

Thanks Tara for your input. I agree with your analysis and that is what is so wierd about this problem. The unique constraint is defined before it is referenced in the Foreign Key constraint (see example below):

alter table "Contact" add constraint "Org_Loc_Cntct_FK1" unique (
"Org_ID",
"Loc_ID",
"Cntct_ID")

go

create index "Contact_Cust_Ord_FK1" on "Cust_Order" (
"Org_ID",
"Buyer_Loc_ID",
"Buyer_Cntct_ID")

go

alter table "Cust_Order"
add constraint "Contact_Cust_Order_FK1" foreign key (
"Buyer_Cntct_ID",
"Org_ID",
"Buyer_Loc_ID")
references "Contact" (
"Cntct_ID",
"Org_ID",
"Loc_ID")

go

And the database still gives me the following error:

Msg 1776, Level 16, State 0, Line 2
There are no primary or candidate keys in the referenced table 'Contact' that match the referencing column list in the foreign key 'Contact_Cust_Order_FK1'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-27 : 15:14:15

You can execute the script on blank database

I did just formatting and added two unique constraint..

--create database sqlteam3

create table "Contact" (
"Cntct_ID" int not null,
"Org_ID" int null,
"Loc_ID" int not null,
"Cntct_Eff_Dt" datetime null,
"Cntct_Trmn_Dt" datetime null,
"Cntct_Title" varchar(10) null,
"Cntct_Fir_Nm" varchar(20) null,
"Cntct_Mid_Nm" varchar(20) null,
"Cntct_Lst_Nm" varchar(30) null,
"Cntct_Sfx" varchar(10) null,
"Cntct_Email_Addr" varchar(60) null,
"Cntct_Position" varchar(30) null,
"Cntct_Spouse_Nm" varchar(50) null,
"Cntct_Birth_Dt" datetime null,
"Cntct_Anniv_Dt" datetime null,
"Cntct_Note" text null)

go

alter table "Contact"
add constraint "Contact_PK" primary key ("Cntct_ID")
go

create table "Cust_Shipping" (
"Cust_Ord_ID" int not null,
"Cust_Ship_Loc_Nbr" int not null,
"Ship_to_Org_ID" int null,
"Ship_to_Loc_ID" int null,
"Ship_to_Cntct_ID" int null,
"Cust_Ship_Alt_Addr_Fl" bit null,
"Cust_Ship_Nm" varchar(50) null,
"Cust_Ship_Addr_Ln_1" varchar(60) null,
"Cust_Ship_Addr_Ln_2" varchar(60) null,
"Cust_Ship_City" varchar(20) null,
"Cust_Ship_St" char(2) null,
"Cust_Ship_Zip" char(10) null,
"Cust_Ship_Country" varchar(60) null,
"Cust_Ship_Attn" varchar(50) null,
"Shipper_Org_ID" int null)

go

alter table "Cust_Shipping"
add constraint "Customer_Order_Shipping_PK" primary key ("Cust_Ord_ID", "Cust_Ship_Loc_Nbr")

go

create table "Organization" (
"Org_ID" int not null,
"Org_Nm" varchar(50) null,
"Org_Typ_Cd" char(1) null,
"Org_Stat_Cd" char(1) null,
"Org_Wbst_URL" varchar(60) null,
"Org_HP_Donor_Fl" bit null)

go

alter table "Organization"
add constraint "Organization_PK" primary key ("Org_ID")

go

create table "Cust_Order_Item" (
"Cust_Ord_ID" int not null,
"Cust_Ord_LI_Nbr" int not null,
"Cust_Ship_Loc_Nbr" int null,
"Cust_Ord_LI_Dlvry_Dt" datetime null,
"Prod_ID" varchar(20) null,
"Cust_Prod_Nbr" varchar(20) null,
"Cust_Prod_Desc" varchar(200) null,
"Cust_Ord_LI_Qty" int null,
"Cust_Ord_LI_UM" char(2) null,
"Cust_Ord_LI_Unit_Price" decimal(12,4) null,
"Cust_Ord_LI_Total" smallmoney null,
"Cust_Ord_LI_Need_Dt" datetime null,
"Cust_Ord_LI_Orig_Ord_Nbr" varchar(20) null,
"Cust_Ord_LI_Inv_Cmttd_Fl" bit null,
"Cust_Ord_LI_Stat_Cd" char(1) null,
"Cust_Ord_LI_Flfld_Qty" int null,
"Cust_Ord_LI_Shpd_Qty" int null,
"Cust_Ord_LI_Wgt" decimal(12,4) null)

go

alter table "Cust_Order_Item"
add constraint "Customer_Order_Item_PK" primary key ("Cust_Ord_ID", "Cust_Ord_LI_Nbr")

go

create table "Location" (
"Loc_ID" int not null,
"Org_ID" int not null,
"Loc_Eff_Dt" datetime null,
"Loc_Trmn_Dt" datetime null,
"Loc_Nm" varchar(60) null,
"Loc_Addr_Ln_1" varchar(60) null,
"Loc_Addr_Ln_2" varchar(60) null,
"Loc_City" varchar(60) null,
"Loc_State" char(2) null,
"Loc_Zip" char(10) null,
"Country_Cd" char(3) null,
"Org_Typ_Cd" char(1) null)

go

alter table "Location"
add constraint "Location_PK" primary key ("Loc_ID")

go

create table "Cust_Order" (
"Cust_Ord_ID" int not null,
"Org_ID" int null,
"Buyer_Loc_ID" int null,
"Buyer_Cntct_ID" int null,
"Cust_Ord_Ntrnl_Fl" bit null,
"Cust_Ord_Intl_Fl" bit null,
"Cust_Ord_PO_Nbr" varchar(20) null,
"Cust_Ord_PO_Rev_Nbr" varchar(20) null,
"Ord_Stat_Cd" char(1) null,
"Cust_Ord_Dt" datetime null,
"Cust_Ord_Need_Dt" datetime null,
"Cust_Ord_Disp_Dt" datetime null,
"Cust_Ord_Total_Amt" smallmoney null,
"Cust_Ord_Disc_Prcnt" decimal(10,2) null,
"Elim_Invoice_Nbr" varchar(20) null,
"Bill_to_Org_ID" int not null,
"Bill_to_Loc_ID" int not null,
"Cust_Ord_Pay_Trms" varchar(15) null,
"Cust_Ord_FOB" varchar(15) null,
"Cust_Ord_Freight_Trms" varchar(15) null,
"Cust_Ord_Tax_Ex_Fl" bit null,
"Cust_Ord_Ship_Via" varchar(20) null,
"Cust_Ord_Conf_Fl" bit null,
"Cust_Ord_Note" text null,
"Cust_Ord_Rcvr" varchar(30) null,
"Cntct_ID" int null)

go

alter table "Cust_Order"
add constraint "Customer_Order_PK" primary key ("Cust_Ord_ID")

go

alter table "Contact" add constraint "Org_Loc_Cntct_FK1" unique (
"Org_ID",
"Loc_ID",
"Cntct_ID")

go

create index "Cust_Ord_Cust_Ord_Item_FK1" on "Cust_Order_Item" (
"Cust_Ord_ID")
go

create unique index "Org_Loc_FK1" on "Location" (
"Org_ID",
"Loc_ID")

go

create index "Cust_Cust_Order_FK1" on "Cust_Order" (
"Org_ID")

go

create index "Loc_Cust_Ord_FK1" on "Cust_Order" (
"Bill_to_Org_ID",
"Bill_to_Loc_ID")
go

create index "Contact_Cust_Ord_FK1" on "Cust_Order" (
"Org_ID",
"Buyer_Loc_ID",
"Buyer_Cntct_ID")
go

--Bohra Added new constraint

alter table "Location"
add constraint "Location_UQ" unique ("Loc_ID","org_id")
go

--- Till here

alter table "Contact"
add constraint "Location_Contact_FK1" foreign key (
"Loc_ID",
"Org_ID")
references "Location" (
"Loc_ID",
"Org_ID")
go

alter table "Contact"
add constraint "Organization_Contact_FK1" foreign key (
"Org_ID")
references "Organization" (
"Org_ID")

go

alter table "Cust_Shipping"
add constraint "Customer_Order_Customer_Order_Shipping_FK1" foreign key (
"Cust_Ord_ID")
references "Cust_Order" (
"Cust_Ord_ID")

go

--Bohra Added new constraint

alter table "Contact"
add constraint "Contact_UQ" unique ("Cntct_ID","Org_ID","Loc_ID")
go

--- Till here

alter table "Cust_Shipping"
add constraint "Contact_Cust_Shipping_FK1" foreign key (
"Ship_to_Cntct_ID",
"Ship_to_Org_ID",
"Ship_to_Loc_ID")
references "Contact" (
"Cntct_ID",
"Org_ID",
"Loc_ID")
go

alter table "Cust_Order_Item"
add constraint "Customer_Order_Customer_Order_Item_FK1" foreign key (
"Cust_Ord_ID")
references "Cust_Order" (
"Cust_Ord_ID")

go

alter table "Cust_Order_Item"
add constraint "Cust_Shipping_Cust_Order_Item_FK1" foreign key (
"Cust_Ord_ID",
"Cust_Ship_Loc_Nbr")
references "Cust_Shipping" (
"Cust_Ord_ID",
"Cust_Ship_Loc_Nbr")

go

alter table "Location"
add constraint "Organization_Location_FK1" foreign key (
"Org_ID")
references "Organization" (
"Org_ID")

go

alter table "Cust_Order"
add constraint "Location_Cust_Order_FK1" foreign key (
"Bill_to_Loc_ID",
"Bill_to_Org_ID")
references "Location" (
"Loc_ID",
"Org_ID")
/*
Msg 1776, Level 16, State 0, Line 334
There are no primary or candidate keys in the referenced table 'Location' that
match the referencing column list in the foreign key 'Location_Cust_Order_FK1'.
Msg 1750, Level 16, State 0, Line 334
Could not create constraint. See previous errors.
*/go

alter table "Cust_Order"
add constraint "Contact_Cust_Order_FK1" foreign key (
"Buyer_Cntct_ID",
"Org_ID",
"Buyer_Loc_ID")
references "Contact" (
"Cntct_ID",
"Org_ID",
"Loc_ID")

go

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-27 : 15:32:41

quote:
Originally posted by lueylump

Thanks Tara for your input. I agree with your analysis and that is what is so wierd about this problem. The unique constraint is defined before it is referenced in the Foreign Key constraint (see example below):

alter table "Contact" add constraint "Org_Loc_Cntct_FK1" unique (
"Org_ID",
"Loc_ID",
"Cntct_ID")

go

create index "Contact_Cust_Ord_FK1" on "Cust_Order" (
"Org_ID",
"Buyer_Loc_ID",
"Buyer_Cntct_ID")

go

alter table "Cust_Order"
add constraint "Contact_Cust_Order_FK1" foreign key (
"Buyer_Cntct_ID",
"Org_ID",
"Buyer_Loc_ID")
references "Contact" (
"Cntct_ID",
"Org_ID",
"Loc_ID")

go

And the database still gives me the following error:

Msg 1776, Level 16, State 0, Line 2
There are no primary or candidate keys in the referenced table 'Contact' that match the referencing column list in the foreign key 'Contact_Cust_Order_FK1'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

1. You forgot to remove the CREATE INDEX part from the script.
2. The foreign key definition does not match the unique constraint definition. Notice the columns aren't in the same order. That's why you are getting that error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lueylump
Starting Member

25 Posts

Posted - 2010-04-27 : 16:03:45

Bohra and Tara, thank you so very much for your patience and tremendous assistance! It worked!!!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-27 : 16:09:22

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-27 : 23:18:01

You are welcome