FK Constraint

Hi,
My Master table structure
CREATE TABLE [dbo].[GR_CHANNEL_M] (
[CHANNELNO] [varchar] (50) NOT NULL ,
[SERVICE] [varchar] (50) NOT NULL ,
[IVR] [varchar] (50) NOT NULL CONSTRAINT [GR_CHANNEL_M_PK] PRIMARY KEY
CLUSTERED ([IVR],[CHANNELNO]),
[OPTIONAL1] [varchar] (50) NULL ,
[OPTIONAL2] [varchar] (50) NULL )
GO
My transaction table
CREATE TABLE [dbo].[GR_CALL_DETAILS_M] (
[CALLID] [varchar] (50) NOT NULL constraint GR_CALL_DETAILS_M_PK primary
key clustered(CALLID),
[DATEANDTIME] [datetime] NULL ,
[DURATION] [numeric](10, 0) NULL ,
[IVR] [varchar] (50) NOT NULL ,
[CHANNELNO] [varchar] (50) NOT NULL CONSTRAINT
[CHANNEL_M_SCHEMECHANGE_T_FK1] FOREIGN KEY ([CHANNELNO])
REFERENCES [dbo].[GR_CHANNEL_M] ([CHANNELNO]),
[CALLTERMINATE] [varchar] (50) NULL)
If I create the table GR_CALL_DETAILS_M i am getting the error
There are no primary or candidate keys in the referenced table
'dbo.GR_CHANNEL_M' that match the referencing column list in the foreign key
'CHANNEL_M_SCHEMECHANGE_T_FK1'.
How to solve this?
thanks
vanithaYour foreign key is not linked to a primary key or unique constraint. For
example, if the primary key of gr_channel_m were channelno,
gr_call_details_m would work just fine.
Ben Nevarez
"vanitha" <vanitha@.discussions.microsoft.com> wrote in message
news:8B38AC26-7E65-4CCD-BBFA-BBB8BDF5A0AA@.microsoft.com...
> Hi,
> My Master table structure
> CREATE TABLE [dbo].[GR_CHANNEL_M] (
> [CHANNELNO] [varchar] (50) NOT NULL ,
> [SERVICE] [varchar] (50) NOT NULL ,
> [IVR] [varchar] (50) NOT NULL CONSTRAINT [GR_CHANNEL_M_PK] PRIMARY KEY
> CLUSTERED ([IVR],[CHANNELNO]),
> [OPTIONAL1] [varchar] (50) NULL ,
> [OPTIONAL2] [varchar] (50) NULL )
> GO
> My transaction table
> CREATE TABLE [dbo].[GR_CALL_DETAILS_M] (
> [CALLID] [varchar] (50) NOT NULL constraint GR_CALL_DETAILS_M_PK primary
> key clustered(CALLID),
> [DATEANDTIME] [datetime] NULL ,
> [DURATION] [numeric](10, 0) NULL ,
> [IVR] [varchar] (50) NOT NULL ,
> [CHANNELNO] [varchar] (50) NOT NULL CONSTRAINT
> [CHANNEL_M_SCHEMECHANGE_T_FK1] FOREIGN KEY ([CHANNELNO])
> REFERENCES [dbo].[GR_CHANNEL_M] ([CHANNELNO]),
> [CALLTERMINATE] [varchar] (50) NULL)
> If I create the table GR_CALL_DETAILS_M i am getting the error
> There are no primary or candidate keys in the referenced table
> 'dbo.GR_CHANNEL_M' that match the referencing column list in the foreign
> key
> 'CHANNEL_M_SCHEMECHANGE_T_FK1'.
> How to solve this?
> thanks
> vanitha|||"vanitha" <vanitha@.discussions.microsoft.com> wrote in message
news:8B38AC26-7E65-4CCD-BBFA-BBB8BDF5A0AA@.microsoft.com...
> Hi,
> My Master table structure
>
> If I create the table GR_CALL_DETAILS_M i am getting the error
> There are no primary or candidate keys in the referenced table
> 'dbo.GR_CHANNEL_M' that match the referencing column list in the
foreign key
> 'CHANNEL_M_SCHEMECHANGE_T_FK1'.
> How to solve this?
> thanks
> vanitha
Vanitha,
The tables slightly realigned for readability:
CREATE TABLE [dbo].[GR_CHANNEL_M] (
[CHANNELNO] [varchar] (50) NOT NULL ,
[SERVICE] [varchar] (50) NOT NULL ,
[IVR] [varchar] (50) NOT NULL
CONSTRAINT [GR_CHANNEL_M_PK]
PRIMARY KEY CLUSTERED ([IVR], [CHANNELNO]),
[OPTIONAL1] [varchar] (50) NULL ,
[OPTIONAL2] [varchar] (50) NULL )
GO
My transaction table
CREATE TABLE [dbo].[GR_CALL_DETAILS_M] (
[CALLID] [varchar] (50) NOT NULL constraint
GR_CALL_DETAILS_M_PK primary
key clustered(CALLID),
[DATEANDTIME] [datetime] NULL ,
[DURATION] [numeric](10, 0) NULL ,
[IVR] [varchar] (50) NOT NULL ,
[CHANNELNO] [varchar] (50) NOT NULL
CONSTRAINT [CHANNEL_M_SCHEMECHANGE_T_FK1]
FOREIGN KEY ([CHANNELNO])
REFERENCES [dbo].[GR_CHANNEL_M] ([CHANNELNO]),
[CALLTERMINATE] [varchar] (50) NULL)
Table GR_CHANNEL_M has a PRIMARY KEY of ([IVR], [CHANNELNO]).
You cannot create a foreign key back to the CHANNELNO column because
it is not a key in its own right, but only a *part* of a key, and
has no identifiable or enforced uniqueness.
Create GR_CHANNEL_M as (or use ALTER):
CREATE TABLE [dbo].[GR_CHANNEL_M] (
[CHANNELNO] [varchar] (50) NOT NULL ,
[SERVICE] [varchar] (50) NOT NULL ,
[IVR] [varchar] (50) NOT NULL
CONSTRAINT [GR_CHANNEL_M_PK]
PRIMARY KEY CLUSTERED ([IVR], [CHANNELNO]),
[OPTIONAL1] [varchar] (50) NULL ,
[OPTIONAL2] [varchar] (50) NULL
,CONSTRAINT GR_CHANNEL_M_UNI_CHANNELNO
UNIQUE (CHANNELNO) )
Note the new CONSTRAINT at the end of the DDL.
The second table can be created after this change is added.
Sincerely,
Chris O.|||if i enforce te unique key, that means that channelno is unique, in my logic
channelno is not unique, only channelno with that ivr is unique.
thanks
vanitha
"Chris2" wrote:

> "vanitha" <vanitha@.discussions.microsoft.com> wrote in message
> news:8B38AC26-7E65-4CCD-BBFA-BBB8BDF5A0AA@.microsoft.com...
> foreign key
> Vanitha,
> The tables slightly realigned for readability:
> CREATE TABLE [dbo].[GR_CHANNEL_M] (
> [CHANNELNO] [varchar] (50) NOT NULL ,
> [SERVICE] [varchar] (50) NOT NULL ,
> [IVR] [varchar] (50) NOT NULL
> CONSTRAINT [GR_CHANNEL_M_PK]
> PRIMARY KEY CLUSTERED ([IVR], [CHANNELNO]),
> [OPTIONAL1] [varchar] (50) NULL ,
> [OPTIONAL2] [varchar] (50) NULL )
> GO
> My transaction table
> CREATE TABLE [dbo].[GR_CALL_DETAILS_M] (
> [CALLID] [varchar] (50) NOT NULL constraint
> GR_CALL_DETAILS_M_PK primary
> key clustered(CALLID),
> [DATEANDTIME] [datetime] NULL ,
> [DURATION] [numeric](10, 0) NULL ,
> [IVR] [varchar] (50) NOT NULL ,
> [CHANNELNO] [varchar] (50) NOT NULL
> CONSTRAINT [CHANNEL_M_SCHEMECHANGE_T_FK1]
> FOREIGN KEY ([CHANNELNO])
> REFERENCES [dbo].[GR_CHANNEL_M] ([CHANNELNO]),
> [CALLTERMINATE] [varchar] (50) NULL)
>
> Table GR_CHANNEL_M has a PRIMARY KEY of ([IVR], [CHANNELNO]).
> You cannot create a foreign key back to the CHANNELNO column because
> it is not a key in its own right, but only a *part* of a key, and
> has no identifiable or enforced uniqueness.
>
> Create GR_CHANNEL_M as (or use ALTER):
>
> CREATE TABLE [dbo].[GR_CHANNEL_M] (
> [CHANNELNO] [varchar] (50) NOT NULL ,
> [SERVICE] [varchar] (50) NOT NULL ,
> [IVR] [varchar] (50) NOT NULL
> CONSTRAINT [GR_CHANNEL_M_PK]
> PRIMARY KEY CLUSTERED ([IVR], [CHANNELNO]),
> [OPTIONAL1] [varchar] (50) NULL ,
> [OPTIONAL2] [varchar] (50) NULL
> ,CONSTRAINT GR_CHANNEL_M_UNI_CHANNELNO
> UNIQUE (CHANNELNO) )
> Note the new CONSTRAINT at the end of the DDL.
> The second table can be created after this change is added.
>
> Sincerely,
> Chris O.
>
>|||Then perhaps you can do this:
CREATE TABLE [dbo].[GR_CHANNEL_M] (
[CHANNELNO] [varchar] (50) NOT NULL ,
[SERVICE] [varchar] (50) NOT NULL ,
[IVR] [varchar] (50) NOT NULL CONSTRAINT [GR_CHANNEL_M_PK] PRIMARY KEY
CLUSTERED ([IVR],[CHANNELNO]),
[OPTIONAL1] [varchar] (50) NULL ,
[OPTIONAL2] [varchar] (50) NULL )
CREATE TABLE [dbo].[GR_CALL_DETAILS_M] (
[CALLID] [varchar] (50) NOT NULL constraint GR_CALL_DETAILS_M_PK primary
key clustered(CALLID),
[DATEANDTIME] [datetime] NULL ,
[DURATION] [numeric](10, 0) NULL ,
[IVR] [varchar] (50) NOT NULL ,
[CHANNELNO] [varchar] (50) NOT NULL,
CONSTRAINT [CHANNEL_M_SCHEMECHANGE_T_FK1] FOREIGN KEY ([IVR], [CHANNELNO])
REFERENCES [dbo].[GR_CHANNEL_M] ([IVR], [CHANNELNO]),
[CALLTERMINATE] [varchar] (50) NULL)
-- Ben Nevarez
"vanitha" <vanitha@.discussions.microsoft.com> wrote in message
news:D3BE275B-701A-458B-9761-A53440BCCFDD@.microsoft.com...
> if i enforce te unique key, that means that channelno is unique, in my
> logic
> channelno is not unique, only channelno with that ivr is unique.
> thanks
> vanitha
> "Chris2" wrote:
>|||"vanitha" <vanitha@.discussions.microsoft.com> wrote in message
news:D3BE275B-701A-458B-9761-A53440BCCFDD@.microsoft.com...
> "Chris2" wrote:
>
the
<snip>
> if i enforce te unique key, that means that channelno is unique,
in my logic
> channelno is not unique, only channelno with that ivr is unique.
> thanks
> vanitha
>
vanitha,
Then you cannot have a foreign key reference back to it.
May I ask if you intended for GR_CALL_DETAILS_M to reference both
IVR and CHANNELNO?
CREATE TABLE [dbo].[GR_CALL_DETAILS_M] (
[CALLID] [varchar] (50) NOT NULL constraint
GR_CALL_DETAILS_M_PK primary
key clustered(CALLID),
[DATEANDTIME] [datetime] NULL ,
[DURATION] [numeric](10, 0) NULL ,
[IVR] [varchar] (50) NOT NULL ,
[CHANNELNO] [varchar] (50) NOT NULL,
[CALLTERMINATE] [varchar] (50) NULL,
CONSTRAINT [CHANNEL_M_SCHEMECHANGE_T_FK1]
FOREIGN KEY ([IVR], [CHANNELNO])
REFERENCES [dbo].[GR_CHANNEL_M] ([IVR], [CHANNELNO])
)
Sincerely,
Chris O.