SQL Server Performance

a composite key or separate P key

Discussion in 'T-SQL Performance Tuning for Developers' started by ghemant, May 29, 2005.

  1. ghemant Moderator

    I have table A in which I have 3 columns a, b and c all of the columns I have PK and the question is , how its is treated ? a composite key or separate P key ?

    hsGoswami
    ghemant@gmail.com
  2. dineshasanka Moderator

    bit confuse
    Can you explain this bit more

    quote:I have table A in which I have 3 columns a, b and c all of the columns I have PK and the question is , how its is treated ? a composite key or separate P key ?
  3. surendrakalekar New Member

    It will be 'composite primary key'.
  4. FrankKalis Moderator

  5. ghemant Moderator

    still the question arise will out these keys any of key trated seprately to have relationship with another table (pk & fk )?

    hsGoswami
    ghemant@gmail.com
  6. surendrakalekar New Member

    You mean any column in the 'composite primary key' set with the other tables primary key.

    If I understand your question......
    Yes you can set the relation.
  7. ghemant Moderator

    Dear Sir,
    i am asking i have Table A with 3 column (c1,c2 and c3) is having a composite PK.. and i have another table B for whom i want to create relationship with and in table B i want to build relationship using only c1 from table A as Primary key for table B.
    are u getting me sir ?

    hsGoswami
    ghemant@gmail.com
  8. FrankKalis Moderator

    If your PK consists of three columns A,B,C all of these columns needs to be in the other table as foreign key to establish a relation.

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
  9. surendrakalekar New Member

    No, You can not create a relationship, but you can use join in SQL statement.

    ---------------------------------

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Table1]
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Table2]
    GO

    CREATE TABLE [dbo].[Table1] (
    [a1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [b1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [d1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Table2] (
    [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [d1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
    CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
    (
    [a1],
    [b1],
    [c1]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD
    CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
    (
    [c1]
    ) ON [PRIMARY]
    GO

  10. ghemant Moderator

    Thanx sir
    now i understand

    hsGoswami
    ghemant@gmail.com

Share This Page