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.

first 5 words

From a column of datatype either text or varchar how can I get a definite number of words?
Suppose it got -'I am a BA from Calcutta.Currently I am in Delhi'
I want to retreive first 5 words which will provide me the output-
'I am a BA from'

How can I do that?

SubhasishI imagine something like...

CREATE FUNCTION FindFifthWord
(@.str varchar(1000))
RETURNS int
AS
BEGIN
DECLARE @.SpacesFound INT
DECLARE @.LastSpacePosition INT
SET @.SpacesFound = 0
SET @.LastSpacePosition = 0

WHILE (@.SpacesFound < 5)
BEGIN
IF (CHARINDEX(' ', @.str, @.LastSpacePosition + 1) = 0)
BREAK

ELSE
BEGIN
SET @.LastSpacePosition = CHARINDEX(' ', @.str, @.LastSpacePosition + 1)
SET @.SpacesFound = @.SpacesFound + 1
END

END

RETURN @.LastSpacePosition
END|||No sorry this will not help me.
I may have ' , ' or '.' or space in the words.
How can I remove those?
And I am not getting the required output.

Subhasish|||Well it has to be delimited by something...in my case in the UDF I used space as the delimeter...

There has to be something...otherwise how would you decide to split it up?

In a sentence, the natural delimeter would be a space.|||create function dbo.fn_N_NumberOfWords (
@.phrase nvarchar(4000),
@.HowMany int ) returns nvarchar(4000)
as begin
declare @.i int, @.pos int
declare @.tbl table (cnt int not null, pos int not null)

set @.phrase = replace(replace(@.phrase, '.', '. '), ' ', ' ')

select @.i = 1, @.pos = 1
while @.i <= @.HowMany begin
set @.pos = charindex(' ', @.phrase + ' ', @.pos+1)
insert @.tbl select @.i, @.pos-1
set @.i = @.i + 1
end
return (select substring(@.phrase, 1, pos) from @.tbl where cnt = @.HowMany)
end
go
select dbo.fn_N_NumberOfWords('I am a BA from Calcutta.Currently I am in Delhi', 4)|||Yeah...I found the one I built...

CREATE FUNCTION udf_WORDS
(@.str varchar(8000), @.Del char(1)=' ')
RETURNS int
AS

/* Mimic REXX function */
BEGIN
DECLARE @.Words INT, @.Pos INT, @.x Int
SELECT @.Words = 0, @.Pos = 1, @.x = -1

WHILE (@.x <> 0)
BEGIN
SET @.x = CHARINDEX(@.Del, @.str, @.Pos)
SET @.Pos = @.x + 1
SET @.Words = @.Words + 1
END

RETURN @.Words
END|||if you're just counting words, then you don't have to have a loop:

alter function dbo.fn_CountWords (
@.phrase nvarchar(4000),
@.delimiter nchar(1) ) returns int
as begin
set @.phrase = rtrim(ltrim(@.phrase))
if cast(reverse(@.phrase) as nchar(1)) != @.delimiter
set @.phrase = rtrim(ltrim(@.phrase)) + @.delimiter
return (
select (datalength(@.phrase) - datalength(replace(@.phrase, @.delimiter, '')))/2
)
end
go|||Very cool...

Just remove the delimiter and compare the string lendths...|||I like the concept. I think you need some logic to account for duplicate delimiters, such as two spaces between sentences.

subhasishray appears to want a function that returns the first five (or N?) words as a string. I think a loop cannot be avoided in solving this.

Create function NWords(@.CharString varchar(500), @.NWords int)
returns varchar(500)
as
begin
declare @.TempString varchar(500)
set @.CharString = ltrim(rtrim(@.CharString))
set @.TempString = @.CharString + ' '
while @.NWords > 0
begin
set @.TempString = ltrim(right(@.TempString, len(@.TempString) + 1 - CharIndex(' ', @.TempString)))
set @.NWords = @.NWords - 1
end
return left(@.CharString, len(@.CharString) - len(@.TempString))
end
GO

print dbo.NWords('This is my string, just for sample purposes. You are free to copy it if your like.', 5)
GO

Result:
This is my string, just

If he wants commas and periods stripped out, h have to use the REPLACE function.

blindman|||i was talking about avoiding the loop while counting words, not while returning the n number of words.