Assing Column PK/FK after table creation

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 2008 Forums

Transact-SQL (2008)

Assing Column PK/FK after table creation

Author

Topic

s2002
Starting Member

49 Posts

Posted - 2010-10-24 : 10:22:57

Hello
DB Table their Identity column setting as well as column PK/FK.
Is it possible to reassign PK/FK/Identity Column settings

Original Create script for table is some thing like

CREATE TABLE [dbo].[Authors](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](256) NOT NULL,
[Description] [ntext] NOT NULL,
[PubDate] [datetime] NOT NULL,

CONSTRAINT [PK_Authors] PRIMARY KEY CLUSTERED
(
[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] TEXTIMAGE_ON [PRIMARY]
GO

**************************************************

but Now it's some thing like below in my DB:

CREATE TABLE [dbo].[Authors](
[ID] [smallint] NOT NULL,
[Name] [nvarchar](256) NOT NULL,
[Description] [ntext] NOT NULL,
[PubDate] [datetime] NOT NULL,

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

How could I change above script so PK/FK/Autonumber/Identity Columns assigned again to DB .

Sachin.Nand

2937 Posts

Posted - 2010-10-24 : 12:01:23

No its not possible.Only way would be to drop the column ID & recreate it with identity specification.
Till now i just could not understand why it is that way?

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-24 : 13:16:55

Are you sure that you're not just not scripting the identity? If the table was created with the script you first give, it should still have those properties. It's hard to remove an identity.

p.s. You shouldn't be using NText. It's deprecated and will be removed in a future version of SQL. Use nvarchar(max) instead. If you're not expecting unicode data, use varchar and varchar(max)

--
Gail Shaw
SQL Server MVP

s2002
Starting Member

49 Posts

Posted - 2010-10-24 : 23:44:41

Thanks for replies
I Exported Data from original DB to this DB using SQL management Studio. After export completion I noticed it didn't include identity and column relations although I checked check box to do it while exporting Data.
Now , data is imported to my DB but Identity/PK/FK not assigned to columns

Any Idea about this would highly appreciated.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-25 : 01:27:33

Easiest way will probably be to rename the table, create it again properly (with the identity column) then, using identity insert, copy the data over from the old table.

Constraints are easy to add (look up alter table add constraint), identity is not.

--
Gail Shaw
SQL Server MVP

s2002
Starting Member

49 Posts

Posted - 2010-10-25 : 02:10:11

Thank you GilaMonster.

Could anyone please provide me with

1- script which add prefix to all tables in a DB.
2- script which insert all records from one table to another table in the same DB.

Regards,

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-25 : 05:01:48

1) Look up sp_rename
2) look up insert

All tables in the DB?

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources