create foreign key using query

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

Select allOpen 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

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

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

Select allOpen in new window

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.

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

Select allOpen 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

Select allOpen in new window

Then, try the above code again.

Giannis

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

Select allOpen in new window

Giannis

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

Select allOpen 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

Select allOpen 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

Capture.JPG

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

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

Select allOpen in new window

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

Giannis

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

Select allOpen in new window

and send a screenshot?

32 null rows were returned.

Capture.JPG

Hi,

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

Giannis

Giannis,

webcustomers is the parent table.
webaccess is a child table.

Giannis,
attached is the result.

Capture.JPG