Avatar of TrueBlue
TrueBlue
Flag for United States of America asked on

create foreign key using query

Hi!

We have a MSSQL database running on a SQL server 2008.
We can access this database using SSMS.
We need to create a foreign key between two tables.
WebAccess and WebCustomers
Both of these tables have data in them.
WebAccess has a one to many relationship to WebCustomers
The key for WebAccess is Key_CorporateID
The Key for WebCustomers is CorporateIDKey
The column in common is CorporateID

Can someone please create a query that I can execute on SSMS to create the foreign key between these two tables?

TIA
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
TrueBlue

8/22/2022 - Mon
Ioannis Paraskevopoulos

You may check this.

ALTER TABLE [dbo].[WebCustomers]  WITH CHECK ADD  CONSTRAINT [FK_WebCustomers_WebAccess ] FOREIGN KEY([CorporateID])
REFERENCES [dbo].[WebAccess ] ([CorporateID])
GO

ALTER TABLE [dbo].[WebCustomers] CHECK CONSTRAINT [FK_WebCustomers_WebAccess ]
GO

Open in new window


Is there a reason why you don't do this with the designer? It will prompt you to save the change.

Edit
I added the CHECK CONSTRAINT

Giannis
EugeneZ

also in order to successfully create FK, make sure
the column 'dbo.WebAccess .CorporateID' is  the same data type as referencing column 'WebCustomers.CorporateID' in foreign key 'FK_WebCustomers_WebAccess
TrueBlue

ASKER
Giannis,

Designer errors out when I try to save it.
I tried the query and got the following:

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.WebAccess ' that match the referencing column list in the foreign key 'FK_WebCustomers_WebAccess '.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_WebCustomers_WebAccess ' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.

Any ideas?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Ioannis Paraskevopoulos

ALTER TABLE [dbo].[WebCustomers]  WITH CHECK ADD  CONSTRAINT [FK_WebCustomers_WebAccess ] FOREIGN KEY([CorporateIDKey])
REFERENCES [dbo].[WebAccess ] ([Key_CorporateID])
GO

ALTER TABLE [dbo].[WebCustomers] CHECK CONSTRAINT [FK_WebCustomers_WebAccess ]
GO

Open in new window

TrueBlue

ASKER
I changed the name of the key in both tables so they match to CorporateIDKey then ran

ALTER TABLE [dbo].[WebCustomers]  WITH CHECK ADD  CONSTRAINT [FK_WebCustomers_WebAccess ] FOREIGN KEY([CorporateIDKey])
REFERENCES [dbo].[WebAccess ] ([CorporateIDKey])
GO

ALTER TABLE [dbo].[WebCustomers] CHECK CONSTRAINT [FK_WebCustomers_WebAccess ]
GO

and got the following errors:

Foreign key 'FK_WebCustomers_WebAccess ' references invalid column 'CorporateIDKey' in referencing table 'WebCustomers'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_WebCustomers_WebAccess ' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.

I did check that the column type was the same for both CorporateIDs.
It is smallint

Any ideas?
Ioannis Paraskevopoulos

Can you script out these tables and post it here? Right click on each table , select tasks, generate scripts, create.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
TrueBlue

ASKER
sorry for the delay.
router got hit by lightning.

USE [topmssql]
GO

/****** Object:  Table [dbo].[WebAccess]    Script Date: 04/14/2013 00:07:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[WebAccess](
      [RecordNo] [int] IDENTITY(1,1) NOT NULL,
      [loginname] [varchar](25) NOT NULL,
      [loginpassword] [varchar](20) NOT NULL,
      [corporateid] [smallint] NOT NULL,
      [securitylevel] [smallint] NOT NULL,
      [emailaddress] [varchar](50) NOT NULL,
      [userlastname] [varchar](25) NOT NULL,
      [userfirstname] [varchar](20) NULL,
      [lastvisited] [datetime] NULL,
 CONSTRAINT [PK_WebAccess] PRIMARY KEY CLUSTERED
(
      [RecordNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [CorporateIDKey] UNIQUE NONCLUSTERED
(
      [corporateid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [Key_Loginname] UNIQUE NONCLUSTERED
(
      [loginname] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

-----------------------------------------------------------------------------------------------------------

USE [topmssql]
GO

/****** Object:  Table [dbo].[WebCustomers]    Script Date: 04/14/2013 00:08:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[WebCustomers](
      [AR_CUST] [varchar](20) NULL,
      [corporateid] [smallint] NULL,
      [CustomerNumber] [smallint] NOT NULL,
      [CSN] [varchar](10) NULL,
      [SECCSN] [varchar](10) NULL,
      [CustomerName] [varchar](35) NOT NULL,
      [ADDR1] [varchar](30) NOT NULL,
      [ADDR2] [varchar](30) NULL,
      [City] [varchar](17) NOT NULL,
      [State] [varchar](2) NOT NULL,
      [ZipCode] [varchar](10) NOT NULL,
      [COMM_PHONE1] [varchar](14) NULL,
      [ReceiverAccountNumber] [varchar](6) NULL,
      [Phone1] [varchar](14) NULL,
      [Phone2] [varchar](14) NULL,
      [SiteNumber] [varchar](19) NULL,
      [UserManualsCode] [smallint] NULL,
      [RecordNo] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_WebCustomers] PRIMARY KEY CLUSTERED
(
      [RecordNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
Ioannis Paraskevopoulos

Hi,

This is the script generated in my SSMS for this relationship. Give it a try:

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.WebCustomers ADD CONSTRAINT
	FK_WebCustomers_WebAccess FOREIGN KEY
	(
	corporateid
	) REFERENCES dbo.WebAccess
	(
	corporateid
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT

Open in new window


Giannis
Ioannis Paraskevopoulos

If this doesn't work the first time, you may need to create a unique index on corporateid:

CREATE UNIQUE INDEX [IX_corporateid]
ON [dbo].[WebAccess] ( [corporateid] )
GO

Open in new window


Then, try the above code again.

Giannis
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
TrueBlue

ASKER
Giannis,

This is what I got when I ran the query.
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_WebCustomers_WebAccess". The conflict occurred in database "topmssql", table "dbo.WebAccess", column 'corporateid'.
Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Ioannis Paraskevopoulos

Is it possible that there are corporateid in customer that are not in webaccess? Please run the following and tell me if there are any results:

Select distinct corporateid from webcustomers c
Left join webaccess a
On a.corporateid=c.corporateid
Where a.corporateid is null

Open in new window


Giannis
SOLUTION
EugeneZ

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
TrueBlue

ASKER
Giannis,

Is it possible that there are corporateid in customer that are not in webaccess?
Yes, some customers do not have webaccess because they do not have an email address on file.

This is what the query yielded.

Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'corporateid'.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ioannis Paraskevopoulos

You may run this:

Select distinct a.corporateid from webcustomers c
Left join webaccess a
On a.corporateid=c.corporateid
Where a.corporateid is null

Open in new window


It is unnecessary though to run it. You may have a row in WebAccess table with a corporateid, lets say -1 and description 'No Web Access' and then run the folloowing:

UPDATE WebCustomer
SET CorporateId = -1 
WHERE CorporateId IS NULL

Open in new window


This will make all customers have a corporate id and then you will be able to apply the foreign key suggested in :

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28089538.html#a39077621

Giannis
TrueBlue

ASKER
TrueBlue

ASKER
jyparask

Select distinct a.corporateid from webcustomers c
Left join webaccess a
On a.corporateid=c.corporateid
Where a.corporateid is null

The aforementioned image shows the result of this query.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Ioannis Paraskevopoulos

Have you tried updating to -1 and then apply the suggested solution?

Giannis
TrueBlue

ASKER
how do I update to -1?
TIA
Ioannis Paraskevopoulos

Take a backup and be very careful...
Then run the following:
UPDATE WebCustomer
SET CorporateId = -1 
WHERE CorporateId IS NULL

Open in new window


This will update each row with a NULL CorporateId to -1

Giannis
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
TrueBlue

ASKER
Giannis,

It ran successfully but said 0 records affected.
Any ideas?
Ioannis Paraskevopoulos

Can you run this:
Select a.* 
from webcustomers c
Left join webaccess a
On a.corporateid=c.corporateid
Where a.corporateid is null

Open in new window


and send a screenshot?
TrueBlue

ASKER
32 null rows were returned.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
TrueBlue

ASKER
Ioannis Paraskevopoulos

Hi,

This is an old issue and i am a bit confused...Which is the parent table and which is the child one?

Giannis
TrueBlue

ASKER
Giannis,

webcustomers is the parent table.
webaccess is a child table.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
TrueBlue

ASKER
Giannis,
attached is the result.
TrueBlue

ASKER