Results 1 to 4 of 4

Thread: Problem with FK

  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: 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 can´t 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!

    /E

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Client
    ----------------
    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);
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    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
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Feb 2004
    Posts
    4

    Thumbs up Oh yes!

    Wow!
    That´s perfect! Works fine!
    Thank you very much.

    /E





    Originally posted by Enigma
    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •