How to make composite primary key?

Question

How to make composite primary key like column_1 + '-' + column_2, selected columns are from another table ?

This is not clear. PS A simple key has one column, a composite key has more than one column. What you describe is a simple key not a composite key. — philipxy, Aug 09 '20 at 06:21

score 1 · Answer 1 · answered Aug 08 '20 at 11:53

1

A primary key consists of columns in the table where the primary key is defined, not in another table. You would define it as:

create table t (
    column_1 <type>,
    column_2 <type>,
    primary key (column_1, column_2)
);

You don't specify the type, so <type> is the type of the columns.

I suspect that you are creating a junction table and these are foreign key references to other tables. That looks like:

create table t (
    column_1 <type>,
    column_2 <type>,
    constraint fk_t_column_1 foreign key (column_1) references t1(column_1),
    constraint fk_t_column_2 foreign key (column_2) references t1(column_2),
    primary key (column_1, column_2)
);

A primary key -- or its components -- can have foreign key references to other tables.

answered Aug 08 '20 at 11:53

Gordon Linoff

1,242,037
58
646
786

I have to do like this: create table t( column_PK primary key ); and from existing table x have to insert values from two columns in format x.column_1 + '-' + x.column_2 – Adi_c7 Aug 08 '20 at 11:59
1

That's not a composite primary key since you want to concatenate some fields into a single field. Is this homework? If so can you add the question details? The question I have here is why do you need to do this? – Charleh Aug 08 '20 at 12:06
key_id is a combination of values order_id and item_id that contains the middle dash between the values. For example, in a table order_items order_id is 1615, item_id is 3 and key_id will be 1615-3. – Adi_c7 Aug 08 '20 at 12:06
@Charleh it's an exam task – Adi_c7 Aug 08 '20 at 12:15
Nope - you confuse the actual constraint with your desired presentation of that information. The dash does not make the 2 columns any more unique than the 2 columns alone. – SMor Aug 08 '20 at 12:54
@SMor i know that, but that was a task. – Adi_c7 Aug 08 '20 at 12:56
@Smor . . . It actually makes the column *less* unique -- consider if the values might contain hyphens. – Gordon Linoff Aug 08 '20 at 20:13

score 0 · Answer 2 · edited Aug 08 '20 at 15:55

Primary keys are created using data definition language (DDL), which is used to create or modify objects. It looks like what you are trying to do is create a primary key using data manipulation language (DML), which include SELECT statements. This isn't possible.

Also, it looks like you are trying to create a single primary key by combining concatenating column1 + '-' + column2. A composite primary key consists of multiple columns. The - here is extraneous because it looks like its being used for formatting and doesn't relate to anything else in the row.

score -2 · Answer 3 · edited Aug 09 '20 at 02:10

insert into items(key_id, order_id, item_id, product_id, quantity, list_price, discount)
select cast(sales.order_items.order_id as varchar(4)) + '-' +  cast(sales.order_items.item_id as varchar(4)), sales.order_items.order_id,
sales.order_items.item_id, sales.order_items.product_id, sales.order_items.quantity, sales.order_items.list_price, sales.order_items.discount
from sales.order_items

How to make composite primary key?

3 Answers3