Share auto-incremented primary key between two tables

Question

1.00/5 (1 vote)

See more:

SQL-Server-2014

i need to share the id value for both tables like
df
1
2
3
5
6
10
mf
4
7
8
9

What I have tried:

i have created a auto increment number for one table
CREATE TABLE df (
id IDENTITY(1,1)
);

CREATE TABLE mf (
id IDENTITY(1,1)
);

Posted 18-Jan-18 19:29pm

Pasupathy Msc

Updated 18-Jan-18 21:48pm

Add a Solution

Comments

RDBurmon 19-Jan-18 1:41am

so you like to insert record in first table "df" with auto increment ID (suppose 1) and then would like to insert the record in second table "mf" with similar id like in table "DF" (which is also 1)

am I correct?

Pasupathy Msc 30-Jan-18 7:41am

Yes sir

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-01-18T21:48:00

No, you don't want to do that. The idea of an IDENTITY field is that it is a unique identifier of a row within a table - and multiple tables will have different rows with different identities.
The only time you would want or need the same row identity for each row in two different tables is when you have badly designed your system, creating two tables where one is needed. The solution to that is simple: make it one table. If you don't, then the multiuser aspect of SQL Server is going to bite you - and badly - when you get to production.
If there is intended to be a correlation between the two tables, then that's simple: each table had a separate ID value (which doesn't have to be the same) but one table has a column which contains a Foreign Key relationship with the other and which contains the ID of the row in the second table:

CustNames
ID      INT, IDENTITY
CName   NVARCHAR(255)

ID      Cname
1       Jones Designs
2       Smiths Instruments
3       Joes Chips

CustAddresses
ID      INT, IDENTITY
Addr    NVARCHAR(1024)
CID     INT, FOREIGN KEY to CustNames.ID

ID      Addr                            CID
1       Smiths House, Smiths Lane       2
2       JonesTown                       1
3       High Street, Potato Town, Idaho 3

See what I mean?