how to addd one to one reletionship between 2 tabl

method · June 3rd, 2005, 10:59 AM

Hi guys i wonder if i can add one to one reletionship between 2 tables in sql server 2000 just like access 2000. I be happy if some one teach me how. IN the links below is the pics of my tables.I want to add one to one reletionship between players table and teams.Thanks

http://i5.photobucket.com/albums/y18...letionship.jpg

http://i5.photobucket.com/albums/y18...letionshp2.jpg

r_ganesh76 · June 3rd, 2005, 11:39 PM

Normally One-to-One relationships are not implemented as primary foreign key relationships alone.

You can implement it by having the same primary keys in both the tables

Regards
Ganesh

r_ganesh76 · June 3rd, 2005, 11:42 PM

this is an example

Code:

CREATE TABLE [dbo].[Name] (
    [ID] [int] NOT NULL ,
    [Name] [char] (10)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Age] (
    [ID] [int] NOT NULL ,
    [Age] [tinyint] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Name] ADD 
    CONSTRAINT [PK_Name] PRIMARY KEY  CLUSTERED 
    (
        [ID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Age] ADD 
    CONSTRAINT [PK_Age] PRIMARY KEY  CLUSTERED 
    (
        [ID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Age] ADD 
    CONSTRAINT [FK_Age_Name] FOREIGN KEY 
    (
        [ID]
    ) REFERENCES [dbo].[Name] (
        [ID]
    )
GO

Regards
Ganesh

joefawcett · June 5th, 2005, 07:44 AM

But what you show isn't, and shouldn't be a one-to-one relationship.
If you are trying to make sure that a player is only in one team at a time then you can set a unique constraint on the player column in the team table.

--

Joe (Microsoft MVP - XML)