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
    ghemant, May 29, 2005
    #2

  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 ?
    dineshasanka, May 29, 2005
    #2

  3. surendrakalekar New Member

    It will be 'composite primary key'.
    surendrakalekar, May 30, 2005
    #2

  4. FrankKalis Moderator

    FrankKalis, May 30, 2005
    #2

  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
    ghemant, May 30, 2005
    #2

  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.
    surendrakalekar, May 30, 2005
    #2

  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
    ghemant, May 30, 2005
    #2

  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
    FrankKalis, May 30, 2005
    #2

  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

    surendrakalekar, May 30, 2005
    #2

  10. ghemant Moderator

    Thanx sir
    now i understand

    hsGoswami
    ghemant@gmail.com
    ghemant, May 30, 2005
    #2

Share This Page