sql server programming: FK Constraint -- sql server programming

Re: FK Constraint

vanitha
11/16/2005 10:28:01 PM

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

[quoted text, click to view]

Re: FK Constraint

Ben Nevarez
11/16/2005 11:16:18 PM

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

[quoted text, click to view]

Re: FK Constraint

Chris2
11/16/2005 11:21:21 PM

[quoted text, click to view]

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.

Re: FK Constraint

Chris2
11/17/2005 7:12:53 PM

[quoted text, click to view]

<snip>

[quoted text, click to view]

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.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.