Constrains - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Constrains

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2009-08-04 : 08:34:00

Hello,

I have the following table:


create table dbo.PostsTags
(
  PostId uniqueidentifier not null,
  TagId uniqueidentifier not null,
    constraint PK_PostsTags primary key clustered (PostId, TagId),
    constraint FK_PostsTags_Posts foreign key(PostId) references Posts(Id) on delete cascade,
    constraint FK_PostsTags_Tags foreign key(TagId) references Tags(Id) on delete cascade
) -- PostsTags

I want to create the constrains after the the table.


alter table dbo.PostsTags 
add constraint PK_PostsTags primary key clustered (PostId, TagId),
add constraint FK_PostsTags_Posts foreign key(PostId) references Posts(Id) on delete cascade,
add constraint FK_PostsTags_Tags foreign key(TagId) references Tags(Id) on delete cascade
go

1) Is this the correct way to do it?

2) Should I also define the Primary Key constraint of my tables this way or should I create it within the table and leave only the constrains between tables to be defined after it (I think this is more correct, right)?

3) Also related to constrains I have been seeing a lot BIT columns with a constraint:


IsAnonymous bit not null constraint DF_User_IsAnonymous default (0)

Why is a constraint named DF is added to Bit columns?

Thanks,
Miguel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-04 : 09:38:26

1, you can do it both ways
2, the common way is to create pk with table definition and then create fks separately
3, its a default constraint which sets default value of bit field as 0. its just given a name starting with DF to denote default

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2009-08-04 : 09:54:22

For the constraints on the table can I do the following:


create table dbo.Assets
(
  Id uniqueidentifier rowguidcol not null
    constraint DF_Assets_Id default (newid()),
    constraint PK_Assets primary key clustered,
  Content nvarchar(max) not null,	
  Locked bit not null default 0,
  [Name] nvarchar(100) not null
) -- Assets

I am not sure if this is how usually is done.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-04 : 09:57:07

did you try running this? what happened when you tried?

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2009-08-04 : 10:49:44

quote:
Originally posted by visakh16

did you try running this? what happened when you tried?

No. Sorry I thought it would. I got an error and changed my code to:


create table dbo.Assets
(
  Id uniqueidentifier rowguidcol not null constraint DF_Assets_Id default (newid()),    
  Content nvarchar(max) not null,	
  Locked bit not null constraint DF_Assets_Locked default (0),
  [Name] nvarchar(100) not null,
    constraint PK_Assets primary key clustered (id),
) -- Assets

I keep the Default constraints in the same line as the column and at the end I add the Primary Key constraint and specify the column name.

The table was created.

Is this ok?

Thanks,
Miguel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources