Primary and Foreign key problems

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Primary and Foreign key problems

Author

Topic

PaulNeil
Starting Member

9 Posts

Posted - 2007-07-16 : 00:20:34

Hi,
I am just new to SQL Server. Here is the create statements for tables I have created.

CREATE TABLE [T1] (
[ID] IDENTITY(1000, 2) NOT NULL,
[NAME] VARCHAR(100),
[SERV_TYPE] VARCHAR(40),
[NWT] VARCHAR(40),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [T1_ID] PRIMARY KEY ([T1_ID])
)
GO

CREATE TABLE [T2] (
[ID] INT NOT NULL,
[CONTACT_TYPE] VARCHAR(1),
[CONTACT_NAME] VARCHAR(40),
[CONTACT_1] VARCHAR(100),
[CONTACT_2] VARCHAR(50),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [PK_T2] PRIMARY KEY ([ID])
)
GO

ALTER TABLE [T2] ADD CONSTRAINT [T1_T2]
FOREIGN KEY ([ID]) REFERENCES [T1] ([ID])
GO

My problem here how can I get T1.ID to be automatically available or copied into T2.ID, when I have opened the table, T2 for data entry.

Thanks you guys for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-16 : 00:28:10

1. insert record into T1
2. use @@identity or scope_identity() to get the identity value (refer to BOL for more details and difference between these 2)
3. use the identity value retrieved from Step 2 to insert into T2

KH
[spoiler]Time is always against us[/spoiler]

PaulNeil
Starting Member

9 Posts

Posted - 2007-07-16 : 01:27:27

Hi Khatan,

Would it be possible for you to show how to do this, with a code, as I'm very new to SQL server. I am also using SQL Server 2005 Express, and I have tried everything but no joy so far. Thank you very much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-16 : 02:34:53

[code]DECLARE @ID int

INSERT INTO T1 (NAME, SERV_TYPE, NWT)
SELECT 'test', 'abc', 'def'

SELECT @ID = scope_identity()

INSERT INTO T2 (ID, CONTACT_1, CONTACT_2)
SELECT @ID, '123', '456'[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-16 : 02:36:38

also error in your DDL.

quote:


CREATE TABLE [T1] (
[ID] INT IDENTITY(1000, 2) NOT NULL,
[NAME] VARCHAR(100),
[SERV_TYPE] VARCHAR(40),
[NWT] VARCHAR(40),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [T1_ID] PRIMARY KEY ([T1_ID] [ID])
)
GO

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources