开发者

What is the SQL to create a table where its PK is also a FK?

Say I have:

create table Post(
  PostID int not null constraint PK_Post primary key clustered,
  Title nvarchar(200) not null
) on [primary]

create table PostDetail(
  PostID int not null constraint PK_PostDetail primary key clustered,
  Text nvarchar(max) null
) on [primary]

How do I mak开发者_运维技巧e PostDetail.PostID an FK referencing Post.PostID?


Use:

ALTER TABLE POSTDETAIL
  ADD CONSTRAINT fk_post 
      FOREIGN KEY (postid) REFERENCES POST (postid)

Though I have to say that what you've listed looks to be a one-to-one relationship - only one POSTDETAIL record associates with a POST record. You might as well use:

create table Post(
  PostID int not null constraint PK_Post primary key clustered,
  Title nvarchar(200) not null,
  Text nvarchar(max) null
) on [primary]


If you want to make a proper 1-1 relationship, that's harder. Currently, you can still have an entry in [Post] that doesn't have an entry in [PostDetail].

If you want to go one step further, you may want to research Tony Rogerson's recent investigations into the problem, at http://sqlblogcasts.com/blogs/tonyrogerson/archive/2010/01/23/how-to-create-a-one-to-one-relationship-in-sql-server-using-dri-triggers-and-views.aspx


After the fact:

alter table PostDetail
    add constraint FK_PostDetail_Post 
        foreign key (PostID) references Post (PostID)

Or in the table def:

create table PostDetail(
  PostID int not null constraint PK_PostDetail primary key clustered,
  Text nvarchar(max) null,
  constraint FK_PostDetail_Post foreign key (PostID) references Post (PostID)
) on [primary]


Create a new table with the following fields.

Title ID (PK, FK – Title Table) Publisher_ID (PK, FK – Pub table) Valid - bit Status (Unlimited)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜