August 1, 2008 at 6:30 am
Table Objects contains two columns ObjectID (primary key) and TypeID.
My second table contains three columns TypeID and LanguageCode (as clustered primary keys) and Description.
How do I add a relationship between these tables? When I try to add this in Management Studio, I get the message "both sides of a relationship must have the same number of columns"
Should I arrange the keys differently or should I implement any other type of constraint?
/Glenn
CREATE TABLE [dbo].[OBJECT](
[ObjectID] [int] IDENTITY(1,1) NOT NULL,
[TypeID] [int] NULL,
[ObjectName] [nvarchar](20) NULL,
CONSTRAINT [PK_OBJECT] PRIMARY KEY CLUSTERED
(
[ObjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[OBJECT_TYPE](
[TypeID] [int] NOT NULL,
[LangCode] [nchar](10) NOT NULL,
[Name] [varchar](50) NULL,
CONSTRAINT [PK_OBJECT_TYPE_1] PRIMARY KEY CLUSTERED
(
[TypeID] ASC,
[LangCode] 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
ALTER TABLE [dbo].[OBJECT_TYPE] WITH CHECK ADD CONSTRAINT [FK_OBJECT_TYPE_OBJECT_TYPE] FOREIGN KEY([TypeID], [LangCode])
REFERENCES [dbo].[OBJECT_TYPE] ([TypeID], [LangCode])
GO
ALTER TABLE [dbo].[OBJECT_TYPE] CHECK CONSTRAINT [FK_OBJECT_TYPE_OBJECT_TYPE]
August 1, 2008 at 7:22 am
If TypeID and LangCode are the PK's that are meant to be used to link to the Object table (parent to child), then the Object table needs to also have the LangCode column. That's all. An alternative is that you can make the TypeID a unique constraint (assuming it is unique) and then set up a foreign key from the Ojbect table to the Type table through the constraint.
"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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply