November 30, 2019 at 2:51 am
All
I have two tables=
Master table
id_master, int, Autoincremental
detail_table
id_master
id_detail,int Autoincremental
My question is, in the detail table, how create a table that the id_detail start in 1, when a id_master is new?
Example
Master_table
id_master
1
2
3
detail_table
id_master id_detail
1 1
1 2
1 3
2 1
2 2
3 1
How do I do it every time a new id is added in the "master" start of 1 in the "detail"?
November 30, 2019 at 5:34 pm
Lookup SEQUENCE - instead of using IDENTITY for the detail table. It will require code changes to support - but it can do what you are requesting.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 1, 2019 at 4:14 pm
Lookup SEQUENCE - instead of using IDENTITY for the detail table. It will require code changes to support - but it can do what you are requesting.
I thought that a SEQUENCE would be used for the creation of non-repeating numbers? Can you describe in more detail how you think one could be used here?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 1, 2019 at 5:29 pm
>> I have two tables= <<
Really? Then where's the DDL for these two tables? Why do you think an identifier can be numeric? What math are you doing on them, you're probably not old enough to remember, but the term "master" refers to pointer chains in the old pre-relational network databases. In RDBMS we have "referencing" and "referenced" tables that are not really the same thing but close enough. Here's an attempt at posting the DDL you didn't know how to do. Oh, by the way, there's no such thing as "Autoincremental" in SQL. The closest we come is the CREATE SEQUENCE statement. You might want to look it up. This statement has a lot of options for start and stop values, cycles, datatypes, etc. I wrote an article on it; https://www.red-gate.com/simple-talk/sql/learn-sql-server/sql-server-sequence-basics/.
CREATE SEQUENCE Foobar_Sequence
AS INTEGER
START WITH 1
INCREMENT BY 1
NO CYCLE;
CREATE TABLE Foobars
(foo_id CHAR(10) NOT NULL PRIMARY KEY,
...);
CREATE TABLE Foobar_Details
(foo_id CHAR(10) NOT NULL
REFERENCES Foobars(foo_id)
ON UPDATE CASCADE -- please pick the appropriate business rules for DRI
ON DELETE CASCADE,
foo_detail_seq INTEGER NOT NULL,
PRIMARY KEY (foo_id, foo_detail_seq),
...);
>> My question is, in the detail table, how create a table that the id_detail start in 1, when a id_master is new? <<
INSERT INTO Foobars
VALUES
('ABC', NEXT VALUE FOR Foobar_Sequence, ...);
You will need to create a sequence for each of your foobars, assuming that each one is contained within the scope of a single foobar_id. If they are global, you would not need to do this.
Please post DDL and follow ANSI/ISO standards when asking for help.
December 2, 2019 at 4:19 pm
Unless you are required to do this for legal purposes, it's more trouble than it's worth. Just assign identity or a date/time field when you create the record and then use a view with a ROW_NUMBER() to display it in this format.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 2, 2019 at 4:22 pm
Unless you are required to do this for legal purposes, it's more trouble than it's worth. Just assign identity or a date/time field when you create the record and then use a view with a
ROW_NUMBER()to display it in this format.Drew
+1 to this.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply