PK and Relation - 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

General SQL Server Forums

New to SQL Server Programming

PK and Relation

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2012-12-10 : 08:45:43

Hello,

I have two tables:


create table dbo.Table1
(
  Table1_Id int identity not null
    constraint Table1_Id_PK primary key clustered (Table1_Id)
);
create table dbo.Table2
(
  Table2_Id int not null,  
  Table2_Code nvarchar (40) not null
)

I am splitting a bigger TABLE into Table1 and Table2.
The Table2_Id value is the same as Table1_Id value.
The PK in Table2 should be { Table2_Id, Table2_Code }.
This combination is unique ...

A few possible rows would be:

TABLE 1
Table1_Id
1
2

TABLE 2
Table2_Id  Code
1              A        > PK = (1, A)    (Table1_ID = 1)
1              B        > PK = (1, B)    (Table1_ID = 1)
2              A        > PK = (2, A)    (Table1_ID = 2)

How should I define the relation between the two tables and the PK in Table 2?

Thank You

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-10 : 08:51:43

PK in table2 is Table2_Id, Table2_Code.

FK in table2 is Table2_Id references Table1_Id.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2012-12-10 : 10:46:41

You mean something like:


create table dbo.Table1
(
  Table1_Id int identity not null
    constraint Table1_Id_PK primary key clustered (Table1_Id)
);
create table dbo.Table2
(
  Table2_Id int not null,  
  Table2_Code nvarchar (40) not null,
    constraint Table2_Id_Code_PK primary key clustered unique (Table2_Id, Table2_Code)
)

alter table dbo.Table2
add constraint Table2_Id_FK foreign key (Table2_Id) references dbo.Table1(Table1_Id) on delete cascade on update cascade;

I am not completly sure about Table2 PK. I should make it primary key clustered unique?

Thank You

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-10 : 10:54:37

Yes PK on both fields as Nigelrivett said. Do you need on delete cascade on update cascade?

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2012-12-10 : 11:39:26

quote:
Originally posted by sodeep

Yes PK on both fields as Nigelrivett said. Do you need on delete cascade on update cascade?

Well, when an item in Table 1 is deleted I want all items in Table 2 to be deleted. But when a item in Table 2 is deleted I do not want the "parent" item in Table 1 do be deleted. Am I doing it right?

Yes I have:
constraint Table2_Id_Code_PK primary key clustered unique (Table2_Id, Table2_Code)

I am just not sure why is I should have unique and/or clustered.

Thank You

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-10 : 12:24:01

You said it is the row identifier so should be the PK.
Whether it is clustered or not is up to you.

Do you want entries in table2 to be deleted when an entry in table1 is deleted or for the table1 delete to fail if there are referencing entries in table2.
I would do it via a stored procedure and delete table2 before table1 if that's what you want.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2012-12-10 : 16:10:29

quote:
Originally posted by nigelrivett

Do you want entries in table2 to be deleted when an entry in table1 is deleted or for the table1 delete to fail if there are referencing entries in table2.

I only want entries in Table 2 to be deleted when an entry in Table 1 is delete. Not the other way around.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-11 : 04:15:02

Then define the FK with cascade or use an SP for the delete.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources