How you define 1:1 relation ship between two table in oracle and sql database?

One table's PK is also the FK pointing to another's PK. Note: the first one can't be a sequence or auto incremented.

what if the first table's primary key is a seqence or auto incremented? "what to do then?"

The PK of the child table is totally dependent on the values of the PK of the parent table. Thus it can't be set automatically, instead it should reference values already used in the parent table PK.

Hi,

I wanted to make 1:1 relationship between login and Users table, but it did not helped what you said earlier, here is the detail what i am trying to achieve.

I had login table having PK, and users table FK to login table and identity column Id (PK). This was showing relationship M:1 before, so i asked you how i can make 1:1 relationship between this table. What i understand from your reply is by removing identity column of child table and making that PK will create 1:1 relationship. I have removed identity column from Users, so Login table has Id - PK, Users has Login Id as PK & FK to login table. Now relationship has been changed to 1:N.

Any thoughts how i can make 1:1 relationship between this table?

To make it clearer: you should end up with a setup like this:

USER

UserId (PK) FirstName LastName Email ...

LOGIN

UserId(PK). FK to User.UserId Username Password ... Here I assume that a User should exist before its Login. You can make User.UserId AutoInc, but you cannot do that with Login.UserId since it references User.UserId.

With that setup, you will obtain 1:1 relationship. If you have some other FK, like:

USER

UserId (PK) FirstName LastName Email ...

LOGIN

LoginId(PK) UserId(FK to User.UserId) Username Password ... ... then you need to make a Unique Constraint on Login.UserId in order to obtain 1:1 relationship, otherwise it will be Login.UserId (m:1) User.UserId.