|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
one to (one or zero) relationshipHow do I represent a one to (one or zero) relationship?
This is a puzzle to me. The purpose of having the relationship this way is I want Table1 to be able to accept an insert of a PK even when there is not a related FK in Table2. I can set the FK as a unique, but when I create the relationship it makes it a one to one relationship. If I don't create the relationship, then I won't be able to have updates and deletes cascade. Sorry that I don't have table definitions, I can't figure out how to get it to generate the scripts from 2005 express the way I used to use Query analyzer on 2000. pk - fk,pk
Thats one to (one or zero) relatioship . abhishek Show quote "shuma***@cs.fsu.edu" wrote: > How do I represent a one to (one or zero) relationship? > > This is a puzzle to me. The purpose of having the relationship this > way is I want Table1 to be able to accept an insert of a PK even when > there is not a related FK in Table2. I can set the FK as a unique, but > when I create the relationship it makes it a one to one relationship. > If I don't create the relationship, then I won't be able to have > updates and deletes cascade. > > Sorry that I don't have table definitions, I can't figure out how to > get it to generate the scripts from 2005 express the way I used to use > Query analyzer on 2000. > > A PK in one table and an FK and PK in another table could be various
types of relationships, that doesn't specify cardinality. Abhishek Pandey wrote: Show quote > pk - fk,pk > Thats one to (one or zero) relatioship . > > > abhishek > > "shuma***@cs.fsu.edu" wrote: > > > How do I represent a one to (one or zero) relationship? > > > > This is a puzzle to me. The purpose of having the relationship this > > way is I want Table1 to be able to accept an insert of a PK even when > > there is not a related FK in Table2. I can set the FK as a unique, but > > when I create the relationship it makes it a one to one relationship. > > If I don't create the relationship, then I won't be able to have > > updates and deletes cascade. > > > > Sorry that I don't have table definitions, I can't figure out how to > > get it to generate the scripts from 2005 express the way I used to use > > Query analyzer on 2000. > > > > As suggested by david use some other tool to view the relationship
implemented by the suggested approach. create a pk in say table1 on say Table1Column1 then refer the Table1Column1 as a FK in Table2 on Table2Column2 In addition also implemented uniquieness in Table2Column2 by either implementing PK or unique constraint abhishek Show quote "shuma***@cs.fsu.edu" wrote: > A PK in one table and an FK and PK in another table could be various > types of relationships, that doesn't specify cardinality. > > Abhishek Pandey wrote: > > pk - fk,pk > > Thats one to (one or zero) relatioship . > > > > > > abhishek > > > > "shuma***@cs.fsu.edu" wrote: > > > > > How do I represent a one to (one or zero) relationship? > > > > > > This is a puzzle to me. The purpose of having the relationship this > > > way is I want Table1 to be able to accept an insert of a PK even when > > > there is not a related FK in Table2. I can set the FK as a unique, but > > > when I create the relationship it makes it a one to one relationship. > > > If I don't create the relationship, then I won't be able to have > > > updates and deletes cascade. > > > > > > Sorry that I don't have table definitions, I can't figure out how to > > > get it to generate the scripts from 2005 express the way I used to use > > > Query analyzer on 2000. > > > > > > > > shuma***@cs.fsu.edu wrote:
> How do I represent a one to (one or zero) relationship? A foreign key IS a constraint that represents a "one to zero or more"> > This is a puzzle to me. The purpose of having the relationship this > way is I want Table1 to be able to accept an insert of a PK even when > there is not a related FK in Table2. I can set the FK as a unique, but > when I create the relationship it makes it a one to one relationship. > If I don't create the relationship, then I won't be able to have > updates and deletes cascade. > > Sorry that I don't have table definitions, I can't figure out how to > get it to generate the scripts from 2005 express the way I used to use > Query analyzer on 2000. relationship. CREATE TABLE tbl1 (col1 INT PRIMARY KEY); CREATE TABLE tbl2 (col1 INT NOT NULL FOREIGN KEY (col1) REFERENCES tbl1 (col1), /* ??? PRIMARY KEY */); INSERT INTO tbl1 VALUES (1) ; To enforce uniqueness in both tables, just add a unique key to tbl2: CREATE TABLE tbl2 (col1 INT NOT NULL UNIQUE FOREIGN KEY (col1) REFERENCES tbl1 (col1), /* ??? PRIMARY KEY */); I think you are struggling with the diagram tool in SSIS. That's a pretty shocking way to design tables IMO. Either learn SQL or get a decent CASE tool instead. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- I thank you for your response. Like I said, I have set the FK as
unique, but again, if I setup cascading updates and deletes this creates the constraint that will make the relationship one to one instead of one to (one or zero). I have considered using triggers to create the functionality of cascading deletes and updates, but am not sure if this would be a good design. I admit that I don't have a great deal of design experience, which why I was looking for other perspectives from those more experienced. You imply that I should use SQL instead of the diagramming tool, but did not offer anything constructive in this regard. I do know SQL contrary to your "learn SQL" comment. I hope you reallize that is somewhat insulting, because it is stated on the false assumption that I don't know SQL. It's not an issue of how I get there, but what is the better end design. I can come up with several different designs, and can figure out the SQL or whatever it takes to get me there. But what I'm worried about is which designs will be difficult to manage, and was hoping someone that had been down this road can shed light on what has worked for them. shuma***@cs.fsu.edu wrote:
> I thank you for your response. Like I said, I have set the FK as Not exactly. It will delete referencing rows when the parent is deleted> unique, but again, if I setup cascading updates and deletes this > creates the constraint that will make the relationship one to one > instead of one to (one or zero). but that doesn't stop you creating a 1 to (1 or 0). Example: CREATE TABLE tbl1 (col1 INT PRIMARY KEY); CREATE TABLE tbl2 (col1 INT NOT NULL UNIQUE FOREIGN KEY (col1) REFERENCES tbl1 (col1) ON DELETE CASCADE ON UPDATE CASCADE, /* ??? PRIMARY KEY */); INSERT INTO tbl1 VALUES (1) ; > You imply that I should use SQL instead of the diagramming tool, but I'm sorry if you felt insulted, that wasn't my intention. I very rarely> did not offer anything constructive in this regard. I do know SQL > contrary to your "learn SQL" comment. I hope you reallize that is > somewhat insulting, because it is stated on the false assumption that I > don't know SQL. insult people online although I do try to keep replies short and to the point. I tried to offer a constructive suggestion by posting the SQL CREATE TABLE statements. The rest of my advice was based on experience (or call it prejudice) that there are usually two reasons why people use the diagram tool: either A) They don't know SQL or B) They don't have any better software. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- Thanks for clearing that up David. I didn't reallize that it was still
possible to accomplish with the constraint. I am not sure what I was doing wrong with setting up the constraint, because I have tried it several times on various occasions and when I tested it the parent table wouldn't let me insert unrelated rows, I eventually gave up as I assumed that the constraint was strict in requiring exactly 1 to 1. But armed with the knowledge that it was possible to still use a constraint, I tried again finally able to get it to work the way I wanted. Thanks, and I'm glad there was nothing intentional in regards to your comment. David Portas wrote: Show quote > shuma***@cs.fsu.edu wrote: > > I thank you for your response. Like I said, I have set the FK as > > unique, but again, if I setup cascading updates and deletes this > > creates the constraint that will make the relationship one to one > > instead of one to (one or zero). > > Not exactly. It will delete referencing rows when the parent is deleted > but that doesn't stop you creating a 1 to (1 or 0). Example: > > CREATE TABLE tbl1 > (col1 INT PRIMARY KEY); > > CREATE TABLE tbl2 > (col1 INT NOT NULL UNIQUE > FOREIGN KEY (col1) REFERENCES tbl1 (col1) > ON DELETE CASCADE ON UPDATE CASCADE, > /* ??? PRIMARY KEY */); > > INSERT INTO tbl1 VALUES (1) ; > > > > You imply that I should use SQL instead of the diagramming tool, but > > did not offer anything constructive in this regard. I do know SQL > > contrary to your "learn SQL" comment. I hope you reallize that is > > somewhat insulting, because it is stated on the false assumption that I > > don't know SQL. > > I'm sorry if you felt insulted, that wasn't my intention. I very rarely > insult people online although I do try to keep replies short and to the > point. I tried to offer a constructive suggestion by posting the SQL > CREATE TABLE statements. The rest of my advice was based on experience > (or call it prejudice) that there are usually two reasons why people > use the diagram tool: either A) They don't know SQL or B) They don't > have any better software. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- shuma***@cs.fsu.edu wrote:
> What you describe would be this:> How do I represent a one to (one or zero) relationship? > > This is a puzzle to me. The purpose of having the relationship this > way is I want Table1 to be able to accept an insert of a PK even when > there is not a related FK in Table2. CREATE TABLE Table1 (pk int NOT NULL PRIMARY KEY ) CREATE TABLE Table2 (pk int NOT NULL PRIMARY KEY ,CONSTRAINT FK_Table2_Table1 FOREIGN KEY (pk) REFERENCES Table1(pk) ) You can anything into Table1, but only rows in Table2 that are already present in Table1. Or is your problem different? Gert-Jan Show quote > I can set the FK as a unique, but > when I create the relationship it makes it a one to one relationship. > If I don't create the relationship, then I won't be able to have > updates and deletes cascade. > > Sorry that I don't have table definitions, I can't figure out how to > get it to generate the scripts from 2005 express the way I used to use > Query analyzer on 2000.  |
|||||||||||||||||||||||
Other interesting topics