SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 PK and Relation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shapper
Constraint Violating Yak Guru

446 Posts

Posted - 12/10/2012 :  08:45:43  Show Profile  Reply with Quote
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

Edited by - shapper on 12/10/2012 08:50:20

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/10/2012 :  08:51:43  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

shapper
Constraint Violating Yak Guru

446 Posts

Posted - 12/10/2012 :  10:46:41  Show Profile  Reply with Quote
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

Edited by - shapper on 12/10/2012 10:47:24
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/10/2012 :  10:54:37  Show Profile  Reply with Quote
Yes PK on both fields as Nigelrivett said. Do you need on delete cascade on update cascade?
Go to Top of Page

shapper
Constraint Violating Yak Guru

446 Posts

Posted - 12/10/2012 :  11:39:26  Show Profile  Reply with Quote
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
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/10/2012 :  12:24:01  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

shapper
Constraint Violating Yak Guru

446 Posts

Posted - 12/10/2012 :  16:10:29  Show Profile  Reply with Quote
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.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/11/2012 :  04:15:02  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.

Edited by - nigelrivett on 12/11/2012 04:15:57
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000