Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    24

    Unanswered: Problem with mor than one Primary key for one table

    I Have 3 tables, name them tbl1, tbl2 and tbl3. Next i create table tbl1 and table tbl2 with one primary identity key:
    Code:
    create table tbl1(id int primary key identity(1,1),text varchar(50))
    create table tbl2(id int primary key identity(1,1),text varchar(50))
    go
    Next i want to create table tbl3 with references to table tbl1 and tbl2, where the references to tbl1 and tbl2 form the primary key:
    Code:
    create table tbl3(tbl1id int primary key references tbl1(id),tbl2id int primary key references tbl2(id))
    go
    But it won't work.

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    A table can have only one primary key ..

    From what I understand what you are trying to do is create foreign keys

    Code:
    create table tbl1(id int primary key identity(1,1),text varchar(50))
    create table tbl2(id int primary key identity(1,1),text varchar(50))
    go
    
    create table tbl3(tbl1id int foreign key references tbl1(id),tbl2id int foreign key references tbl2(id))
    go
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Nov 2003
    Posts
    24
    Ehm Well, i can create this using foreign keys the way you like to do, but
    1. I don't want any of the combinations tbl1id and tbl2id present twice in the database, thus i want to use both to create the primary key
    2. When using the table with Access i can't edit the table, cause no primary key is present with your method.


  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    How about this :

    Code:
    use pubs 
    go
    
    create table tbl1(id int primary key identity(1,1),text varchar(50))
    create table tbl2(id int primary key identity(1,1),text varchar(50))
    go
    
    create table tbl3(tbl1id int not null foreign key references tbl1(id),tbl2id int not null foreign key references tbl2(id))
    
    alter table tbl3
    add CONSTRAINT tbl3_key PRIMARY KEY (tbl1id ,tbl2id) 
    go
    
    drop table tbl3
    drop table tbl1
    drop table tbl2
    go
    And this type of primary key is called a composite primary key
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Nov 2003
    Posts
    24

    Smile

    thanks, it works fine

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •