SQL change create to alter syntax errors

Question

I was sent a sql code:

create table dbo.Dokumendid(
  id               int identity not Null primary key,
  idPersonal       int not Null references dbo.Personal on delete cascade,
  Liik             char(1) Not Null,                        -- Liik: L - lepingud, K - koolitused, T - tervisetõendid    
  FailiNimetus     nvarchar(200) not null,                  -- faili nimetus
  LaadimiseKpv     smalldatetime null,                      -- laadimise kpv
  Fail             varbinary(max) null,                     -- fail
  Markus           nvarchar(max) Null,                      -- märkus
  dtCreated        datetime default GetDate() Null,
  UserNameCreated  nvarchar(50) null,
  dtUpdated        datetime  null,
  UserNameUpdated  nvarchar(50) null
)
Go
create index IX_Personal on dbo.Dokumendid(idPersonal) on INDEXES
Go

But we already have table dbo.Dokumendid so when I change create to alter I get error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near '('.

I change it to: alter table dbo.Dokumendid alter column error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'identity'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near '('.

You Can use this Code Above the Create table Statement: DROP TABLE IF EXISTS dbo.Dokumendid Go I think you are trying to add an additional Columns to an Existing Table Else you can use alter Table Add Column Statement to add the Columns to the Existing Table. — Thiyagu
– Thiyagu, Commented Apr 30, 2020 at 7:08

Dale K · Accepted Answer · 2020-04-30 08:01:31Z

If you want to add the foreign key inline:

create table dbo.Dokumendid(
  id               int identity not Null primary key,
  idPersonal       int not Null, --references dbo.Personal on delete cascade,
  Liik             char(1) Not Null,                        -- Liik: L - lepingud, K - koolitused, T - tervisetõendid    
  FailiNimetus     nvarchar(200) not null,                  -- faili nimetus
  LaadimiseKpv     smalldatetime null,                      -- laadimise kpv
  Fail             varbinary(max) null,                     -- fail
  Markus           nvarchar(max) Null,                      -- märkus
  dtCreated        datetime default GetDate() Null,
  UserNameCreated  nvarchar(50) null,
  dtUpdated        datetime  null,
  UserNameUpdated  nvarchar(50) null,
  CONSTRAINT FK_Dokumendid_idPersonal FOREIGN KEY (idPersonal) REFERENCES dbo.Personal (idPersonal) on delete cascade
)
Go

Also, you can add the index creation online, too (from SQL Server 2014):

create table dbo.Dokumendid(
  id               int identity not Null primary key,
  ...
  CONSTRAINT FK_Dokumendid_idPersonal FOREIGN KEY (idPersonal) REFERENCES dbo.Personal (idPersonal) on delete cascade,
  index IX_Personal(idPersonal)
)
Go

Collectives™ on Stack Overflow

SQL change create to alter syntax errors

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related