How to create Composite Key and how to refer it in other table as a foreign key

Question

I have a table following table

create table tblcountry (
  unqid uniqueidentifier 
  name varchar(100)
  isremoved bit
)

I want to create primary key on basis of unqid + isremoved and in which isremoved must be true

i have got another table:

create table tblstate (
  unqid uniqueidentifier,
  name varchar(100)
  f_tblcountry uniqueidentifier,
  isremoved bit
)

the main thing is that i want that when i try to make isremoved field as true or 1 of tabcountry than it should give me error if i have useid it in referenced table tabstate and the record to which it used its isremoved field is not true. and if the tabstate the isremoved is true for that primary key than its should not give any error.

Answer 1

create table tblcountry (
  unqid uniqueidentifier,
  name varchar(100),
  isremoved bit,
  PRImARY KEY (unqid, isremoved)
)


create table tblstate (
    unqid uniqueidentifier,
    name varchar(100),
    f_tblcountry uniqueidentifier,
    isremoved bit,
  )

  CREATE INDEX tblstateref ON tblstate (unqid, isremoved) -- always use index w/ foreign keys

  ALTER TABLE tblstate ADD CONSTRAINT fk FOREIGN KEY (unqid, isremoved) references tblcountry (unqid, isremoved)

Answer 2

i executed your solution its running fine. when i insert data in tabcountry its let me insert but when i try to insert into tabstate its giveing me error

select * from tblcountry 
5CF96D52-994B-45E3-9CF9-1BC948280E57    india 0
AC2AB153-7FBA-48BC-911B-74A178C74FB5    pak 0
763D2186-68BF-4334-AAA1-CCE16E14E6B1    us 0

when i insert into tabstate as below query

insert INto tblstate values (NEWID(),'raj','5CF96D52-994B-45E3-9CF9-1BC948280E57',0)

i am getting following error:

Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "fk". The conflict occurred in database "usecomp", table "dbo.tblcountry". The statement has been terminated.

Answer 3

I want to create primary key on basis of unqid + isremoved and in which isremoved must be true

With regards to values, a primary key only ensures that they are unique. When a primary key is a composite - comprised of multiple columns - it means that the key is any unique combination of all the columns involved. That means if the primary key is both unqid and isremoved - the following would be valid rows in your table:

UNQID                                  |   ISREMOVED
---------------------------------------------------------------
6F9619FF-8B86-D011-B42D-00C04FC964FF   |   1
6F9619FF-8B86-D011-B42D-00C04FC964FF   |   0

Your primary key should not include the isremoved column.

...when I try to make the isremoved field value set to true/1 in the tblcountry table, I should get an error if I have used it in referenced table tblstate if its' isremoved field is not true. and if the tabstate the isremoved is true for that primary key than its should not give any error.

To do this, you need a foreign key reference associating the unqid and isremoved columns in the tblcountry to the unqid and isremoved columns in the tblstate table.

ALTER TABLE tblcountry 
  ADD CONSTRAINT tstate_fk FOREIGN KEY (unqid, isremoved) references tblstate (unqid, isremoved)

Joshua had the right idea, the wrong direction.
But this means that in order to insert a record into tblcountry, you must have the unqid value already in tblstate. A column with a foreign key constraint can be nullable - the value being null won't trip the foreign key constraint - but you can not specify a portion of the foreign key. Either you satisfy the foreign key, or you don't.

I'm not clear on what you were hoping to model - if I had a better idea, I would provide alternatives.