Problem with FK

If I have a table with a combined PK, can I somehow create
a FK to one of the PK?

Example:

Table Client
-------
PK ClientId
PK Version

Table Product
-------
PK ProductId
FK ClientId - Is this possible?! I cant make it work, becaust it says
that ClientId in table Client must be uniqe. But I just want to know that
the ClientId entered in Product is valid and exists in Client.

Please help me!

/EClient
------
ID
Client_ID
Version

Primary Key (ID);
UNIQUE (Client_id, Version);

Products
------
Product_id
Client_ID

Primary Key (Product_ID);
foreign key (client_ID) references Client(ID);|||Use Pubs
go
CREATE TABLE [Client] (
[ClientId] [int] NOT NULL ,
[Version] [int] NOT NULL ,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientId],
[Version]
) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER FUNCTION [udf_ClientIdCheck] (@.ClientId int)
RETURNS INT
AS
BEGIN
DECLARE @.return_value bit
if exists (select * from Client where ClientId = @.ClientId)
set @.return_value = 1
else
set @.return_value = 0
return @.return_value
END
go

CREATE TABLE [Product] (
[Productid] [int] NULL ,
[Clientid] [int] check (dbo.udf_ClientIdCheck(ClientID) =1)
) ON [PRIMARY]
GO

insert into client values(1,1)
insert into product values (1,1)
insert into product values (1,2)
go
select * from product
go
drop table [Client]
drop table [Product]
go

You will need to make a UDF to use in a check constraint|||Wow!
Thats perfect! Works fine!
Thank you very much.

/E

Originally posted by Enigma

Use Pubs
go
CREATE TABLE [Client] (
[ClientId] [int] NOT NULL ,
[Version] [int] NOT NULL ,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientId],
[Version]
) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER FUNCTION [udf_ClientIdCheck] (@.ClientId int)
RETURNS INT
AS
BEGIN
DECLARE @.return_value bit
if exists (select * from Client where ClientId = @.ClientId)
set @.return_value = 1
else
set @.return_value = 0
return @.return_value
END
go

CREATE TABLE [Product] (
[Productid] [int] NULL ,
[Clientid] [int] check (dbo.udf_ClientIdCheck(ClientID) =1)
) ON [PRIMARY]
GO

insert into client values(1,1)
insert into product values (1,1)
insert into product values (1,2)
go
select * from product
go
drop table [Client]
drop table [Product]
go

You will need to make a UDF to use in a check constraint

Problem with FILENAME as @variable

Hi.

I am trying to create a database based on variables from xp_regread.
Somehow I cant pass @.dBDir as FILENAME.

I get the following error-message :
Server: Msg 170, Level 15, State 1, Line 24
Line 24: Incorrect syntax near '@.dBDir'.
What am I doing wrong ?
I think the problem is somehow related to a datatype-problem or
it is simply not possible to pass a variable to a "create
database"-statement.

I have checked the xp_regread-output...naturally :) ...and it is correct.
Hope someone can help :)

regards
Michael

------------
Source-code is as follows:

USE master
GO

DECLARE @.dBDir nvarchar(128)
DECLARE @.dBlog nvarchar(128)

EXECUTE xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\School-Project\IRPF
Database\','dBDir',@.param = @.dBDir OUTPUT

SET @.dBDir =@.dBDir+'\IRPF.mdf'
SET @.dBlog =@.dBDir+'\IRPF.ldf'

CREATE DATABASE IRPF
ON PRIMARY
( NAME = IRPF_db,
FILENAME = @.dBDir)
LOG ON
( NAME = 'IRPF_log',
FILENAME = @.dBlog)
GOMichael (agentmikie@.cFJERNDETTE.dk) writes:
> I get the following error-message :
> Server: Msg 170, Level 15, State 1, Line 24
> Line 24: Incorrect syntax near '@.dBDir'.
> What am I doing wrong ?
> I think the problem is somehow related to a datatype-problem or
> it is simply not possible to pass a variable to a "create
> database"-statement.

The latter. You will have to use dynamic SQL. The short story is:

SELECT @.sql = 'CREATE DATABASE IRPF ON PRIMARY ( NAME = ''IRPF_db'', ' +
' FILENAME = ' + quotename(@.dBDir) + ' LOG ON ( NAME = ' +
''IRPF_log'', FILENAME = ' + quotename(@.dBlog) + ')')
EXEC (@.sql)

The feature demonstrated here, is one to be used with care. For this
kind of thing, it is the right thing, but there are many cases where
dynamic SQL is not the answer. So there is a full story on
http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> The latter. You will have to use dynamic SQL. The short story is:
> SELECT @.sql = 'CREATE DATABASE IRPF ON PRIMARY ( NAME = ''IRPF_db'', ' +
> ' FILENAME = ' + quotename(@.dBDir) + ' LOG ON ( NAME = ' +
> ''IRPF_log'', FILENAME = ' + quotename(@.dBlog) + ')')
> EXEC (@.sql)
> The feature demonstrated here, is one to be used with care. For this
> kind of thing, it is the right thing, but there are many cases where
> dynamic SQL is not the answer. So there is a full story on
> http://www.sommarskog.se/dynamic_sql.html.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Thank you for your reply.

The link seems to be exactly what I was looking for. :)

Regards
Michael.