A little bit of walking instead of just talking. Code you'll see is Oracle, but I guess other databases offer the same or similar options. Note that I don't know Altium Designer.
Question you asked was:
can I combine two or more fields into one field during the same insert statement?
Yes, you can; you already know the operator - it is concatenation. In Oracle, it is either the concat function or double pipe || operator. Here's how.
First, two sample tables (resistors and capacitors):
SQL> create table resistor
2 (id_res varchar2(10) constraint pk_res primary key,
3 name varchar2(10) not null
4 );
Table created.
SQL> create table capacitor
2 (id_cap varchar2(10) constraint pk_cap primary key,
3 name varchar2(10) not null
4 );
Table created.
Sequence will be used to create unique numbers:
SQL> create sequence seqalt;
Sequence created.
Database trigger which creates the primary key value by concatenating a constant (RES for resistors) and the sequence number, left-padded with zeros up to 7 characters in length (so that the full value length is 10 characters):
SQL> create or replace trigger trg_bi_res
2 before insert on resistor
3 for each row
4 begin
5 :new.id_res := 'RES' || lpad(seqalt.nextval, 7, '0');
6 end trg_bi_res;
7 /
Trigger created.
SQL> create or replace trigger trg_bi_cap
2 before insert on capacitor
3 for each row
4 begin
5 :new.id_cap := 'CAP' || lpad(seqalt.nextval, 7, '0');
6 end trg_bi_cap;
7 /
Trigger created.
Let's insert some rows:
SQL> insert into resistor (name) values ('resistor 1');
1 row created.
SQL> select * from resistor;
ID_RES NAME
---------- ----------
RES0000001 resistor 1
Capacitors:
SQL> insert into capacitor (name) values ('capac 1');
1 row created.
SQL> insert into capacitor (name) values ('capac 2');
1 row created.
SQL> select * From capacitor;
ID_CAP NAME
---------- ----------
CAP0000002 capac 1
CAP0000003 capac 2
My suggestion is a view instead of a new table to be used by the Altium Designer - of course, if it is possible (maybe Designer requires a table, and nothing but a table ...):
SQL> create or replace view v_altium (designitemid, name) as
2 select id_res, name from resistor
3 union all
4 select id_cap, name from capacitor;
View created.
SQL> /
View created.
SQL> select * from v_altium;
DESIGNITEM NAME
---------- ----------
RES0000001 resistor 1
CAP0000002 capac 1
CAP0000003 capac 2
You'd now make the Altium Designer read the view and - from my point of view - it should work just fine.
If it has to be a table (let's call it altium), then it would look like this:
SQL> create table altium
2 (designitemid varchar2(10) constraint pk_alt primary key,
3 name varchar2(10)
4 );
Table created.
Triggers will now be changed so that they also insert a row into the altium table (see line #7):
SQL> create or replace trigger trg_bi_res
2 before insert on resistor
3 for each row
4 begin
5 :new.id_res := 'RES' || lpad(seqalt.nextval, 7, '0');
6 insert into altium (designitemid, name) values (:new.id_res, :new.name);
7 end trg_bi_res;
8 /
Trigger created.
SQL> create or replace trigger trg_bi_cap
2 before insert on capacitor
3 for each row
4 begin
5 :new.id_cap := 'CAP' || lpad(seqalt.nextval, 7, '0');
6 insert into altium (designitemid, name) values (:new.id_cap, :new.name);
7 end trg_bi_cap;
8 /
Trigger created.
Let's try it:
SQL> insert into resistor (name) values ('resistor 4');
1 row created.
SQL> insert into resistor (name) values ('resistor 5');
1 row created.
SQL> insert into capacitor (name) values ('capac 5');
1 row created.
Altium table contents reflects contents of resistor and capacitor:
SQL> select * from altium;
DESIGNITEM NAME
---------- ----------
RES0000011 resistor 4
RES0000012 resistor 5
CAP0000013 capac 5
SQL>
However: why do I prefer a view over a table? Because consistency might suffer. What if you delete a row from the capacitor table? You'd have to delete appropriate row from the new altium table as well, and vice versa.
You can't create a foreign key constraint from the altium table to reference primary keys in other tables because as soon as you try to insert a row into the altium table that references resistor, it would fail as there's no such a primary key in capacitor. You can create constraints, but - that's pretty much useless:
SQL> drop table altium;
Table dropped.
SQL> create table altium
2 (designitemid varchar2(10) constraint pk_alt primary key,
3 name varchar2(10),
4 --
5 constraint fk_alt_res foreign key (designitemid) references resistor (id_res),
6 constraint fk_alt_cap foreign key (designitemid) references capacitor (id_cap)
7 );
Table created.
OK, table was successfully created, but - will it work?
SQL> insert into resistor (name) values ('resistor 7');
insert into resistor (name) values ('resistor 7')
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_ALT_CAP) violated - parent key not
found
ORA-06512: at "SCOTT.TRG_BI_RES", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG_BI_RES'
SQL>
Nope, it won't as such a primary key doesn't exist in the capacitor table.
It means that you'd have to maintain consistency manually, and that's always tricky.
Therefore, if possible, use a view.
