T-SQL script to create PK/FK relationship

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)

T-SQL script to create PK/FK relationship

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2009-07-24 : 06:55:58

Hi,

Previously I have done my table-building tasks using the Management Studio UI, but in this instance I want to use T-SQL directly.
Could anyone please give me the code to achieve the following results:

1) Create a new table called [tbl_Regions], with the ID column as the primary key
2) Modify existing table called [tbl_Sites] to add a new column called [regionID], which will be a foreign key for the above (FK Null values allowed if possible)
3) Set the cascade action to NO ACTION for both UPDATE and DELETE

I've got this far but my t-sql knowledge is minimal so I'll let the experts lead the way...


USE [mytable]
GO

CREATE TABLE [tbl_Regions]
	(
	ID INT IDENTITY(1,1),
	customerID int NOT NULL,
	regionName nvarchar(250) NOT NULL,
	regionDesc nvarchar(max) NOT NULL
	)
GO

ALTER TABLE [tbl_Sites]
	ADD [regionID] int NULL
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-24 : 13:40:49

just use alter table...add constraint... to ADD pk and fk. look into books online for syntax and usage

R
Constraint Violating Yak Guru

328 Posts

Posted - 2009-07-24 : 13:48:56

Yeah, the problem is books online is a bit too in-depth. I'm nervous about doing it wrong.

If I do the changes using the UI while the profiler is running, will it reveal the t-sql statements that are being executed?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-24 : 13:54:43

http://doc.ddart.net/mssql/sql70/aa-az_5.htm

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-24 : 15:16:18

try it on your local/dev environment

R
Constraint Violating Yak Guru

328 Posts

Posted - 2009-07-27 : 04:13:03

Performing the database edits using the GUI, with SQL Profiler running in the background showed me the code I needed. Surprisingly, the code needed was much simpler than I imagined...


ALTER TABLE [tbl_Sites] ADD CONSTRAINT
	FK_tbl_Sites_tbl_Regions FOREIGN KEY
		(
		regionID
		) 
	REFERENCES [tbl_Regions]
		(
		ID
		) 
	ON UPDATE CASCADE 
	ON DELETE NO ACTION 
GO

I think this is a good way for newbies to learn the T-SQL syntax from within a comfortable editing environment.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources