Multi column primary key question

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)

Multi column primary key question

Author

Topic

Darkmatter5
Starting Member

17 Posts

Posted - 2012-05-10 : 09:10:10

I have five tables I need help with.

Jobs (
JobID int PRIMARY KEY IDENTITY,
CONSTRAINT FK_JobSubdivision FOREIGN KEY (SubdivisionID) REFERENCES subdivisions(SubdivisionID),
CONSTRAINT FK_JobEmployee FOREIGN KEY (EmployeeID) REFERENCES employees(EmployeeID)
)

clientCompanies (
ClientCompanyID int PRIMARY KEY IDENTITY
)

clientIndividuals (
ClientIndividualID int PRIMARY KEY IDENTITY
)

clients (
ClientIndividualID int NOT NULL,
ClientCompanyID int NOT NULL,
CONSTRAINT PK_ClientID PRIMARY KEY (ClientIndividualID, ClientCompanyID),
CONSTRAINT FK_IndIndID FOREIGN KEY (ClientIndividualID) REFERENCES clientIndividuals(ClientIndividualID),
CONSTRAINT FK_CompIndID FOREIGN KEY (ClientCompanyID) REFERENCES clientCompanies(ClientCompanyID)
)

Now in my last table named jobs2clients, I need to make a relationship between jobs.jobID <=> clients.PK_ClientID. Is this possible? When you create a multi-column primary key does it now exist as a field of which you can now use in table relationships?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 12:22:53

to make matters simple i would have done it this way


clients (
ClientID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
ClientIndividualID int NOT NULL,
ClientCompanyID int NOT NULL,
CONSTRAINT FK_IndIndID FOREIGN KEY (ClientIndividualID) REFERENCES clientIndividuals(ClientIndividualID),
CONSTRAINT FK_CompIndID FOREIGN KEY (ClientCompanyID) REFERENCES clientCompanies(ClientCompanyID)
)


then add the ClientID as a FK to your jobs2clients along with JobID to create a relationship among them. The relationship b/w ClientIndividualID and ClientCompanyID  is already set in clients tables so I think you can avoid propagating it again to jobs2clients table

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources