Hi,

I am using SQL Server 2005.

I have created the tables for my Database through the Management Studio
front end tool.

I can apply a primary key to the tables easily enough. What I am having
trouble with is assigning multiple columns as primary keys and also how to
define foreign keys between tables.

Does anyone have suggesstions how I can achieve this through management
studio?

Thanks In Advance
Macca

Macca (Ma***@discussions.microsoft.com) writes:
CREATE TABLE mytable (mykeycol1   int NOT NULL,
                      mykeycol2   int NOT NULL,
                      someothercol datetime NULL,
                      fkcol1      varchar(3) NULL,
                      fkcol2a     varchar(9) NOT NULL,
                      fkcol2b     datetime   NOT NULL,
    CONSTRAINT pk_mytable PRIMARY KEY NONCLUSTERED (mykeycol1, mykeycol2),
    CONSTRAINT fk1_mytable FOREIGN KEY (fkcol1)
       REFERENCES yourtable (yourkeycol1),
    CONSTRAINT fk2_mytable FOREIGN KEY (fkcol2a, fkcol2b)
       REFERNECS hertable (herkeycol1, herkeycol2))

Run this from a query window.

Yes, I gather that you want to use the Table Designer, but if you find
it difficult to define two-column PKs or FKs in it, the tool is not
likely to be a productivity booster.

If you want to do serious data modelling, get a real tool like
PowerDesigner or Embrocadero. What's in Mgmt Studio is a toy tool.

In any case, you need to know the syntax fluently sooner or later,
so you may just as well start in that end.

Now that's the type of compelling 'feedback' that leads to a better product.
We need more 'civil' disobedience and less silly excuses.

Show quoteHide quote

When was a database last used?
Enterprise Manager Newbie Question
Maintenance Plan on SQL2005
Using dtsrun in stored procedure
"Export data" problems