PK x FK - 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

General SQL Server Forums

New to SQL Server Programming

PK x FK

Author

Topic

santana
Yak Posting Veteran

72 Posts

Posted - 2010-02-23 : 05:38:07

Hi,

one field can be at the same time PK and FK?

Because I have one table with PK = ID, Ref_no and Date.
And with FK = Dept and Empl.

But sometimes for the same ID, Ref_no and Date, I have more than one Department.

Can I include Dept into the PK? And is it still into the FK?

The DDL for the table:

CREATE TABLE [dbo].[tb_investigations](
[ID] [int] NOT NULL,
[Ref_no] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Raised_date] [datetime] NOT NULL,
[Employee_number] [int] NOT NULL,
[Department_number] [int] NOT NULL,
CONSTRAINT [PK_tb_investigations] PRIMARY KEY CLUSTERED
[ID] ASC,
[Ref_no] ASC,
[Raised_date] ASC,
[Employee_number] ASC,
[Department_number] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[tb_investigations] WITH CHECK ADD CONSTRAINT [FK_tb_investigations_tb_department] FOREIGN KEY([Department_number])
REFERENCES [dbo].[tb_department] ([Department_number])
GO
ALTER TABLE [dbo].[tb_investigations] CHECK CONSTRAINT [FK_tb_investigations_tb_department]
GO
ALTER TABLE [dbo].[tb_investigations] WITH CHECK ADD CONSTRAINT [FK_tb_investigations_tb_employee] FOREIGN KEY([Employee_number])
REFERENCES [dbo].[tb_employee] ([Employee_number])
GO
ALTER TABLE [dbo].[tb_investigations] CHECK CONSTRAINT [FK_tb_investigations_tb_employee]

Thanks.

Regards,

santana

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-23 : 05:56:14

If you have different department with same id, refno and date then you must take it in primary key and that department can be in foreign key as well.

Vabhav T

santana
Yak Posting Veteran

72 Posts

Posted - 2010-02-23 : 06:04:49

Hi,

thanks for your replay.

Good! I will take Dept in PK and FK as well.

Regards,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 09:22:10

quote:
Originally posted by santana

Hi,

thanks for your replay.

Good! I will take Dept in PK and FK as well.

Regards,

Along with other columns so that the group make a unique value.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources