SQL Server Forums - Sequence in SQL

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

Register Now and get your question answered!

Username:	Password:
Save Password
Forgot your Password?

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sequence in SQL

New Topic

Reply to Topic

Printer Friendly

Author

Topic

ralgal
Starting Member

3 Posts

Posted - 05/10/2012 : 04:33:55

Hello,

I am new to SQL server so any help would be appreciated.

Here is what i am trying to do: I have to 2 tables with the following strucure

Table 1 (i'm going to name it food)
order_id
product_name

Table 2(electronics)
order_id
product_name

I would like the order_id to be unique in the 2 tables. Meaning if there is order_id=1 in table 1 i cannot have order_id=1 in table 2.

Example:
Customer 1 orders food and is added to the table 1 with order id 123 and product name pizza.
Customer 2 also orders food and is added to table 1 with order_id 124(incremented by 1) and product name lasagnia.
Customer 3 orders electornics and is added to table 2 with order_id 125 and product name samsung.
Customer 4 orders food and is added to table 1 with order_id 126... and so on

Using Auto Increment from SQL is it possible to create an increment for the order_id that is unique for the 2 tables?

So i want to increment order_id in BOTH the tables at the same time and add the information according to the product the customer chose.
Any thoughts on how to do that?

Thank you

Raluca

RickD
Slow But Sure Yak Herding Master

United Kingdom
3560 Posts

Posted - 05/10/2012 : 05:27:48

Yes, use odd for one identity and even for the other

Table 1 = order_id INT IDENTITY(1,2) NOT NULL
Table 2 = order_id INT IDENTITY(2,2) NOT NULL

Alternatively, give them high starting points or even start one going upwards and the other downwards.

Table 1 = order_id INT IDENTITY(1,1) NOT NULL
Table 2 = order_id INT IDENTITY(-1,-1) NOT NULL

or

Table 1 = order_id INT IDENTITY(1,1) NOT NULL
Table 2 = order_id INT IDENTITY(-2147483648,1) NOT NULL

Edited by - RickD on 05/10/2012 05:31:20

robvolk
Most Valuable Yak

USA
15559 Posts

Posted - 05/10/2012 : 08:20:17

And just to strengthen Rick's solutions:

Table 1 = order_id INT IDENTITY(1,2) NOT NULL, CONSTRAINT CHK_Odd CHECK (order_id % 2 = 1)
Table 2 = order_id INT IDENTITY(2,2) NOT NULL, CONSTRAINT CHK_Even CHECK (order_id % 2 = 0)

Table 1 = order_id INT IDENTITY(1,1) NOT NULL, CONSTRAINT CHK_Positive CHECK (order_id > 0)
Table 2 = order_id INT IDENTITY(-1,-1) NOT NULL, CONSTRAINT CHK_Negative CHECK (order_id < 0)

Table 1 = order_id INT IDENTITY(1,1) NOT NULL, CONSTRAINT CHK_Positive CHECK (order_id > 0)
Table 2 = order_id INT IDENTITY(-2147483648,1) NOT NULL, CONSTRAINT CHK_Negative CHECK (order_id < 0)

DonAtWork
Flowing Fount of Yak Knowledge

2111 Posts

Posted - 05/10/2012 : 09:56:40

Since you are new, perhaps you would like to correct your DB design closer to First Normal Form? From the little info you have given us, it looks like you should have an ORDERS table, a PRODUCTS table, and a CUSTOMERS table.

http://en.wikipedia.org/wiki/First_normal_form

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

ralgal
Starting Member

3 Posts

Posted - 05/10/2012 : 10:33:20

Thank you for the information.

I was able to apply the -1,-1 strategy.
That was just an example. Those are not my acutal tables. I just simplified the design and gave an example for better understanding of what i was trying to do.

Thank you for the quick reply.

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.06 seconds.