Create Table - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Create Table

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2010-10-14 : 17:45:07

Hello,

I am creating a table with its constraints as follows:


create table dbo.Profiles
(
  Id int not null
    constraint Profiles_Id_PK primary key clustered (Id),
  Approved bit not null 
    constraint Users_Approved_DF default (0),
  City nvarchar (200) null,
  Curriculum varbinary (max) filestream null,
  [Key] uniqueidentifier rowguidcol not null 
     constraint Profiles_Key_U unique,
) filestream_on [FILE]

I have a few questions:

- Do I need to have "clustered (Id)" or can I have just "clustered"?

- Can I move the constraints to the end of the script instead of having it in each field?

I tried but I always get errors:


create table dbo.Profiles
(
  Id int not null
  Approved bit not null,
  City nvarchar (200) null,
  Curriculum varbinary (max) filestream null,
  [Key] uniqueidentifier rowguidcol not null,
     constraint Profiles_Id_PK primary key clustered (Id), 
     constraint Profiles_Key_U unique (Key),
     constraint Users_Approved_DF default (0) for (Approved)
) filestream_on [FILE]

Any suggestion is also welcome to improve my code.

Thank You,

Miguel

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-15 : 11:33:15

If you want to do it in one shot, I think you just need to move the DEFAULT constraint to the column you want to default. As far as the clustered keyword goes, you'll need the ID, if you want to do it that way. But, the CLUSTERED is optional as a Primary Key is clustered by default. But, it is nice to specify that you indent the PK to be clustered. Here is a working bit of code:

create table dbo.Profiles
(
  Id int not null,
  Approved bit not null constraint Users_Approved_DF default (0),
  City nvarchar (200) null,
  Curriculum varbinary (max) filestream  null,
  [Key] uniqueidentifier rowguidcol not null,
     constraint Profiles_Id_PK primary key clustered (Id), 
     constraint Profiles_Key_U unique ([Key]),
)

Other wise you can do it as a Create and Alter, for example:

create table dbo.Profiles
(
  Id int not null,
  Approved bit not null ,
  City nvarchar (200) null,
  Curriculum varbinary (max) filestream  null,
  [Key] uniqueidentifier rowguidcol not null
); 
go

alter table dbo.Profiles
    add constraint Profiles_Id_PK primary key clustered (Id);
go

alter table dbo.Profiles
    add constraint Profiles_Key_U unique ([Key]);
go

alter table dbo.Profiles
    add constraint Users_Approved_DF default (0) for Approved;
go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources