Post reply

Create Table with multi column primary key and 2 foreign keys from different tables

  • February 20, 2007 at 7:52 pm

    #171460

    Hi,

    I'm trying to learn how to create a table with multi-column primary key where 2 are also foreign keys.  I can do it with Enterprise manager, but want to do it with t-sql using Query Analyzer.  The database is for a project out of a book.  They just give the table columns, size, type and if nulls are allowed or not.  Just a 1 to many from the 2 master tables to the child table.

    Here is what I have come up with so far.  I have reduced the size of the child table to keep the size of the post down.

    drop database cms

    create database cms

    create table tblWorker

      (WorkerID int primary key,

       Name nvarchar(50) null)

    create table tblCustomer

      (CarNo nvarchar(15) primary key,

       Name nvarchar(255) null,

       Address nvarchar(255) null,

       Make nvarchar(50) null)

    create table tblJobDetails

      (CarNo nvarchar(15)

     constraint fCarNo foreign key (CarNo)

         references tblCustomer(CarNo),

       JobDate datetime,

       WorkerID int

     constraint fWorkerID foreign key (WorkerID)

        references tblWorker(WorkerID),

       Tuning int,

       FuelFilter int,

       AirFilter int,

       Remarks nvarchar(255),

       CONSTRAINT primary key (CarNo, JobDate, WorkerID))

    go

    I'm currently getting an error in Query Analyzer on 'CONSTRAINT primary key (CarNo, JobDate, WorkerID))' two lines up.

    Server: Msg 156, Level 15, State 1, Line 41

    Incorrect syntax near the keyword 'primary'.

    I'm trying to do the primary key at the table level and the foreign keys at the column level. 

    Can someone help point me to the correct reference to read on how to do this or let me know what I'm doing wrong?

    Thanks,  Wes

  • February 20, 2007 at 9:07 pm

    #690804

    change line

    CONSTRAINT primary key (CarNo, JobDate, WorkerID))

    to

    primary key (CarNo, JobDate, WorkerID))

  • February 20, 2007 at 9:41 pm

    #690806

    Thank you Malcolm.  That got me going again.  I also had to add the 'use cms' for which database to use.  I had just finished coding in Enterprise Manager and found when i dumped the scrips, it uses Alter Table statements to add the constraints for the relationships for the tables and primary keys.  I do need to go in and make WorkerID a Idenity key for using Auto number for my primary key to tblWorker.

    None of my examples showed the statement that way.  All use Constraint at the beginning that I was looking at.

    Thanks again,

    Wes

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply