SQL Server composite key problem

I have 2 tables:

create table numbers2
(
id1 int IDENTITY(1,1) not null,
id2 int not null,
primary key(id1, id2)
)

and table 2

create table ttt
(
id int identity(1,1) not null,
first_name varchar(50) null,
last_name varchar(50) null,
sex varchar(1) check (sex in ('m', 'f')) null,
number_id int not null,
id_id1 int not null, 
id_id2 int not null,
primary key(id),
constraint fk_numbers_id1 foreign key (id_id1) references numbers2(id1)
on update no action
on delete no action
)

The problem is how to add constraint "fk_numbers_id1" so it will only reference one part of the composite key from table numbers2. Is it possible or there is other solution?

Answers


Create a unique constraint on numbers2.id1:

create table numbers2
(
id1 int IDENTITY(1,1) not null,
id2 int not null,
primary key(id1, id2),
unique(id1)
)

I would like to know why you chose to create the primary key over two columns, where the first column is already unique by itself. I'm sure this violates some kind of Normal Form rule but I can't remember which one.

If you did it in order to cover the data in both columns, then it is unnecessary to do that, just do this instead (assuming that the data in id1 is genuinely unique):

create table numbers2
(
id1 int IDENTITY(1,1) not null,
id2 int not null,
primary key(id1),
)
create index cover_id2 on numbers2(id2) -- no need to include clustered index columns in a covering index

Need Your Help

Best way for read and write a text file

delphi file-io text-files lazarus fpc

I am using the latest version of Lazarus IDE and I have a Memo1 on my TForm1. I have to load a text file in Memo1 and then edit every line of the Memo (I use Memo1.Lines.Strings[i] := ...). At the ...

Enable or disable the next button based on a live text control value in Wix?

user-interface wix controls conditional-statements

I created a custom dialog page in wix and it has a text box. I want to disable the next button of the installer if the text box is empty end enable it if the user has typed a value. The following c...