June 19, 2012 at 9:29 pm
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
June 19, 2012 at 11:48 pm
wendy elizabeth (6/19/2012)
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 only foreign key constraint defined in the code you posted is this.
ALTER TABLE [dbo].[AT] WITH CHECK ADD CONSTRAINT [FK_AT_AT] FOREIGN KEY([AT_id])
REFERENCES [dbo].[AT] ([AT_id])
But I believe this is defining the AT_id field as a foreign key in [dbo].[AT] and not in [dbo.AT_Pin]. You probably need something like this:
ALTER TABLE [dbo].[AT_Pid] WITH CHECK ADD CONSTRAINT [FK_AT_Pid] FOREIGN KEY([AT_id])
REFERENCES [dbo].[AT] ([AT_id])
However I'm not an expert in this area.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 20, 2012 at 4:44 am
I just see this self-referencing foreign key
ALTER TABLE [dbo].[AT] WITH CHECK ADD CONSTRAINT [FK_AT_AT] FOREIGN KEY([AT_id])
REFERENCES [dbo].[AT] ([AT_id])
It joins the same table to itself.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 20, 2012 at 5:09 am
wendy elizabeth (6/19/2012)
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
I think the defined FK_AT_AT is complete nonsence ! ( referring to the column itself in the same table ! )
Which table is considered to be parent and which table is to be child ?
Confusing usage of AT_id which seems to generate its value ( via identity property ) in table AT_Pln :crazy:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 20, 2012 at 7:36 am
Thanks!
June 21, 2012 at 12:50 pm
wendy elizabeth (6/19/2012)
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]
(emphasis added)
Umm...how can you define a primary key on "AT_Plan_id" when the actual field name in the table def is "AT_pln_id"?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply