foreign key in table - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

foreign key in table

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2012-06-19 : 23:31:25

I have two tables that I am questioning in a sql server 2008 r2 database. The table called [dbo].[AT] is the main table. The table called
[dbo].[AT_Pln] can have 1 to 500 rows for every record in the [dbo].[AT].

Can you tell me if the [AT_id] in the [dbo].[AT_Pln] is a foreign key to the [dbo].[AT] table? To me it looks like there is no foreign key setup.
The following is the defintion for the two tables:
USE [dt1]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AT](
(
[AT_Pln_id] [int] IDENTITY(39502,1) NOT FOR REPLICATION NOT NULL,
[AT_id] [int] NULL,
[Cust] [varchar](50) NULL,
CONSTRAINT [PK_AT_Plan] PRIMARY KEY CLUSTERED
(
[AT_Plan_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

go

USE [dt1]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[AT_Pln](
[AT_id] [int] IDENTITY(1,1) NOT NULL,
[Payment_Month_Date] [datetime] NULL,
[AT_Received_Date] [datetime] NULL,
[Comments] [varchar](4000) NULL

CONSTRAINT [PK_AT_ATID] PRIMARY KEY CLUSTERED
(
[AT_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[AT] WITH CHECK ADD CONSTRAINT [FK_AT_AT] FOREIGN KEY([AT_id])
REFERENCES [dbo].[AT] ([AT_id])
GO

ALTER TABLE [dbo].[AT] CHECK CONSTRAINT [FK_AT_AT]
GO

nathans
Aged Yak Warrior

938 Posts

Posted - 2012-06-20 : 01:13:25

Looks like the foreign key is referencing itself:

ALTER TABLE [dbo].[AT] WITH CHECK ADD CONSTRAINT [FK_AT_AT] FOREIGN KEY([AT_id])
REFERENCES [dbo].[AT] ([AT_id])

Nathan Skerl

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2012-06-20 : 09:37:57

Thanks!

nathans
Aged Yak Warrior

938 Posts

Posted - 2012-06-21 : 14:51:08

glad to help, sometimes it takes a 2nd set of eyes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources