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.