none
How to reference a Composite Primary key SQL 2008

Answers

  • Hi ashwebza,

    >> For every cruise there are multiple stations and the problem I am faced with now is once i start doing imports then i get errors cos the users have duplicate station ID's and the reason for this is because they are collecting different variables at every station. So if they happen to collect 12 different variables then the station_id will be duplicated 12 times.


    It is appropriate to add the column [station_id] to the table [Cruise], and make a multiple primary key ([cruise_code], [station_id]) on the table [Cruise], then create a foreign key constraint on the column [station_id]) which references to the table [Station]. Please view the imitation below:

     

     

    --[TableB] is a table equals to [Station]

    CREATE TABLE [dbo].[TableB](

          [station_id] [varchar](20) NOT NULL,

     CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED

    (

          [station_id] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY]

     

    GO

     

     

    --[TableA] is a table equals to [Cruise]

    CREATE TABLE [dbo].[TableA](

          [cruise_code] [varchar](10) NOT NULL,

          [station_id] [varchar](20) NOT NULL,

     CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED

    (

          [cruise_code] ASC,

          [station_id] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY]

     

    GO

     

    ALTER TABLE [dbo].[TableA]  WITH CHECK ADD  CONSTRAINT [FK_TableA_TableB] FOREIGN KEY([station_id])

    REFERENCES [dbo].[TableB] ([station_id])

    GO

     

    ALTER TABLE [dbo].[TableA] CHECK CONSTRAINT [FK_TableA_TableB]

    GO

     

     

    Wednesday, March 30, 2011 3:08 AM

All replies

  • create table #t1 (a int not null ,b int not null)
    ALTER TABLE #t1 ADD CONSTRAINT
     PK_Table_1 PRIMARY KEY CLUSTERED
     (
     a,
     b
     )

    create table #t2 (a int foreign key references #t1(a))


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, March 28, 2011 11:55 AM
  • Hi there,

     

    What I am trying to say is that I have a Composite primary key in one table but I need to reference it in another table. 

     

    How would I do this?


    Ashley Niekerk
    Monday, March 28, 2011 11:58 AM
  • Ashley

    Have you seen my reply? Please post your example and I will try to help you out


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, March 28, 2011 12:01 PM
  • Hi there, 

     

    I have seen your reply but i am using SQL 2008.

     

    This is the problem I am sitting with now! I have created a table called cruise:

     

    CREATE TABLE Cruise

    (

    cruise_code VARCHAR(10) NOT NULL,

    chief_scientist CHAR(100) NOT NULL,

    purpose CHAR (200) NOT NULL,

    voyager_number SMALLINT,

    start_date DATETIME NOT NULL,

    end_date DATETIME NOT NULL,

    geographical_area CHAR(200),

    cruise_comment CHAR(500),

    vessel_code CHAR(10) NOT NULL REFERENCES Vessel,

    PRIMARY KEY (cruise_code)

    )

    GO

     

    And i have created a table called Station:

     

    CREATE TABLE Station

    (

    station_id VARCHAR(20) NOT NULL,

    ships_station_number VARCHAR(20) NOT NULL,

    latitude FLOAT NOT NULL,

    longitude FLOAT NOT NULL,

    date_time DATETIME,

    gmt INT NOT NULL,

    sounding INT,

    pressure FLOAT,

    secchi_depth INT,

    wave_direction INT,

    wave_height FLOAT,

    wave_period INT,

    wind_direction INT,

    wind_speed INT,

    dry_bulb_temperature FLOAT,

    wet_bulb_temperature FLOAT,

    SST FLOAT,

    air_temperature FLOAT,

    air_pressure FLOAT,

    salinity FLOAT,

    humidity FLOAT,

    station_comment CHAR (500),

    cruise_code VARCHAR(10) NOT NULL REFERENCES Cruise,

    property_code INT NOT NULL REFERENCES property_definitions,

    data_type INT NOT NULL REFERENCES data_type_definitions,

    PRIMARY KEY (station_id)

    )

    GO

    For every cruise there are multiple stations and the problem I am faced with now is once i start doing imports then i get errors cos the users have duplicate station ID's and the reason for this is because they are collecting different variables at every station. So if they happen to collect 12 different variables then the station_id will be duplicated 12 times. how would i get around this issue


    Ashley Niekerk
    Monday, March 28, 2011 12:07 PM
  • Extending Uri's example for a composite key:

    CREATE TABLE dbo.Table1(
    	Table1Col1 int NOT NULL
    	,Table1Col2 int NOT NULL
    	,CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED(
    		Table1Col1
    		,Table1Col2)	
    );
    
    CREATE TABLE dbo.Table2(
    	Table2Col1 int NOT NULL
    		CONSTRAINT PK_Table2 PRIMARY KEY CLUSTERED 
    	,Table1Col1 int NOT NULL
    	,Table1Col2 int NOT NULL
    	,CONSTRAINT FK_Table2_Table1 FOREIGN KEY(
    		Table1Col1
    		,Table1Col2)
    		REFERENCES dbo.Table1(
    		Table1Col1
    		,Table1Col2
    	)
    );
    
    

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by JRStern Monday, March 28, 2011 2:59 PM
    Monday, March 28, 2011 12:14 PM
  • Hi there,

     

    thank you for the reply. I am able to create a composite primary key but i would like to know how I would reference it in a different table.

     

    I my Primary key is (cruise_code, station_id) for the table Station then how would i reference this primary key in Table "Property Ranges"?


    Ashley Niekerk
    Monday, March 28, 2011 12:39 PM
  • >>I my Primary key is (cruise_code, station_id) for the table Station then >>how would i reference this primary key in Table "Property Ranges"?

    Exactly as Dan showed you in his example. Table2 is your Property Ranges table, hmm you have showed us the structure of.


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, March 28, 2011 1:41 PM
  • >>I my Primary key is (cruise_code, station_id) for the table Station then >>how would i reference this primary key in Table "Property Ranges"?

    Exactly as Dan showed you in his example. Table2 is your Property Ranges table, hmm you have showed us the structure of.


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, March 28, 2011 1:41 PM
  • I am relatively new to using SQL 2008 and i would like to understand what you telling me but it is difficult as i am not that good with the application as yet.
    Ashley Niekerk
    Monday, March 28, 2011 1:59 PM
  • Hi ashwebza,

    >> For every cruise there are multiple stations and the problem I am faced with now is once i start doing imports then i get errors cos the users have duplicate station ID's and the reason for this is because they are collecting different variables at every station. So if they happen to collect 12 different variables then the station_id will be duplicated 12 times.


    It is appropriate to add the column [station_id] to the table [Cruise], and make a multiple primary key ([cruise_code], [station_id]) on the table [Cruise], then create a foreign key constraint on the column [station_id]) which references to the table [Station]. Please view the imitation below:

     

     

    --[TableB] is a table equals to [Station]

    CREATE TABLE [dbo].[TableB](

          [station_id] [varchar](20) NOT NULL,

     CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED

    (

          [station_id] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY]

     

    GO

     

     

    --[TableA] is a table equals to [Cruise]

    CREATE TABLE [dbo].[TableA](

          [cruise_code] [varchar](10) NOT NULL,

          [station_id] [varchar](20) NOT NULL,

     CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED

    (

          [cruise_code] ASC,

          [station_id] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY]

     

    GO

     

    ALTER TABLE [dbo].[TableA]  WITH CHECK ADD  CONSTRAINT [FK_TableA_TableB] FOREIGN KEY([station_id])

    REFERENCES [dbo].[TableB] ([station_id])

    GO

     

    ALTER TABLE [dbo].[TableA] CHECK CONSTRAINT [FK_TableA_TableB]

    GO

     

     

    Wednesday, March 30, 2011 3:08 AM