Avatar of TrueBlue
TrueBlueFlag 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
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image

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
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

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
Avatar of TrueBlue
TrueBlue
Flag of United States of America image

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?
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

Avatar of TrueBlue
TrueBlue
Flag of United States of America image

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?
Can you script out these tables and post it here? Right click on each table , select tasks, generate scripts, create.
Avatar of TrueBlue
TrueBlue
Flag of United States of America image

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
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
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
Avatar of TrueBlue
TrueBlue
Flag of United States of America image

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.
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
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of TrueBlue
TrueBlue
Flag of United States of America image

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'.
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
Avatar of TrueBlue
TrueBlue
Flag of United States of America image

ASKER

Avatar of TrueBlue
TrueBlue
Flag of United States of America image

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.
Have you tried updating to -1 and then apply the suggested solution?

Giannis
Avatar of TrueBlue
TrueBlue
Flag of United States of America image

ASKER

how do I update to -1?
TIA
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
Avatar of TrueBlue
TrueBlue
Flag of United States of America image

ASKER

Giannis,

It ran successfully but said 0 records affected.
Any ideas?
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?
Avatar of TrueBlue
TrueBlue
Flag of United States of America image

ASKER

32 null rows were returned.
Avatar of TrueBlue
TrueBlue
Flag of United States of America image

ASKER

Hi,

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

Giannis
Avatar of TrueBlue
TrueBlue
Flag of United States of America image

ASKER

Giannis,

webcustomers is the parent table.
webaccess is a child table.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of TrueBlue
TrueBlue
Flag of United States of America image

ASKER

Giannis,
attached is the result.
Avatar of TrueBlue
TrueBlue
Flag of United States of America image

ASKER

Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo