Sequence in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sequence in SQL

Author

Topic

ralgal
Starting Member

3 Posts

Posted - 2012-05-10 : 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

3608 Posts

Posted - 2012-05-10 : 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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-10 : 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
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-10 : 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 - 2012-05-10 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources