asked on

Augument a table with several attributes

I need to add theses

[ServiceID] [int] - (the new way links to a company service)
[FlagRemoved] [bit] NULL DEFAULT ((0))
[AgentRemoved] [nvarchar](50)
[DateRemoved] [datetime]

to the following table.
CREATE TABLE [dbo].[tbl_index_ticket_services](
      [IndexTicketServiceID] [int] IDENTITY(1,1) NOT NULL,
      [TicketID] [int] NULL,
      [ServiceTypeID] [int] NULL,
      [DateAdded] [datetime] NULL,
      [AgentAdded] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DateCreated] [datetime] NULL,
CONSTRAINT [PK_tbl_index_ticket_services] PRIMARY KEY NONCLUSTERED
(
      [IndexTicketServiceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

ASKER CERTIFIED SOLUTION

Rob Farley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mathieu_cupryk

ASKER

awesome job.

mathieu_cupryk

ASKER

where did u get tbl_index_ticket_companies?

should it be tbl_index_ticket_services?

mathieu_cupryk

ASKER

we have to undo this

alter table [dbo].[tbl_index_ticket_companies]
add
[ServiceID] int,
[FlagRemoved] bit NULL DEFAULT ((0)),
[AgentRemoved] [nvarchar](50),
[DateRemoved] [datetime] ;

alter table [dbo].[tbl_index_ticket_companies]
add constraint fk_Ticket_Service foreign key (ServiceID)
references dbo.tbl_Services (ServiceID) ;

Rob Farley

Oh, sorry... I had read it as being the same table as in the other question. Yes, it should be tbl_index_ticket_services

Did extra columns get created in tbl_index_ticket_companies that you didn't want?

To remove the constraint, use "drop" instead of "add".

alter table [dbo].[tbl_index_ticket_companies]
drop constraint fk_Ticket_Service

And I'm not sure what the table is you need to hook into for your foreign key (if you actual require one at all).

Rob

Rob Farley

And to drop a column:

alter table [dbo].[tbl_index_ticket_companies]
drop column [ServiceID];

mathieu_cupryk

ASKER

what about the constraint?

mathieu_cupryk

ASKER

Msg 5074, Level 16, State 1, Line 1
The object 'fk_Ticket_Service' is dependent on column 'ServiceID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN ServiceID failed because one or more objects access this column.

we must delete the fk and the column.

Rob Farley

Yup... so drop the FK first (see my other comment), and then you can drop the column.

Rob

Rob Farley

ie:

alter table [dbo].[tbl_index_ticket_companies]
drop constraint fk_Ticket_Service;
go
alter table [dbo].[tbl_index_ticket_companies]
drop column [ServiceID];
go