|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
one-to-one relationships in a database diagramhello,
I am trying to figure out how to draw a 1:1 relationship in a database diagram (or with foreign keys) from visual studio 2005. in the books online it explains one-to-one relationships and even shows one in a screenshot, but I haven't found any way of creating them TIA Andy Show quote
"Andy Fish" <ajf***@blueyonder.co.uk> wrote in message Perhaps you mean 1:{0 or 1} rather than 1:1. Concerning two separate tables, news:eVOLXPUHIHA.4040@TK2MSFTNGP02.phx.gbl... > hello, > > I am trying to figure out how to draw a 1:1 relationship in a database > diagram (or with foreign keys) from visual studio 2005. > > in the books online it explains one-to-one relationships and even shows > one in a screenshot, but I haven't found any way of creating them > > TIA > > Andy > > one side is usually optional (unless you pre-populate some data before you add constraints). In the database you just need to put a unique key on top of the foreign key (ie. the optional side of the relationship). Like this: -- 1:{0/1} CREATE TABLE tbl1 (x INT NOT NULL REFERENCES tbl2 (x) UNIQUE, ...); To make it 1:1, do the same on the other table. This is unusual in SQL databases, although not necessarily "wrong" in principle. -- 1:1 CREATE TABLE tbl2 (x INT NOT NULL REFERENCES tbl1 (x) UNIQUE, ...); -- David Portas Show quote
"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message ah thanks - I guess this means I can create it in SQL and it will show up on news:O8CMqpUHIHA.4808@TK2MSFTNGP05.phx.gbl... > "Andy Fish" <ajf***@blueyonder.co.uk> wrote in message > news:eVOLXPUHIHA.4040@TK2MSFTNGP02.phx.gbl... >> hello, >> >> I am trying to figure out how to draw a 1:1 relationship in a database >> diagram (or with foreign keys) from visual studio 2005. >> >> in the books online it explains one-to-one relationships and even shows >> one in a screenshot, but I haven't found any way of creating them >> >> TIA >> >> Andy >> >> > > Perhaps you mean 1:{0 or 1} rather than 1:1. Concerning two separate > tables, one side is usually optional (unless you pre-populate some data > before you add constraints). > > In the database you just need to put a unique key on top of the foreign > key (ie. the optional side of the relationship). Like this: > > -- 1:{0/1} > CREATE TABLE tbl1 (x INT NOT NULL REFERENCES tbl2 (x) UNIQUE, ...); > > To make it 1:1, do the same on the other table. This is unusual in SQL > databases, although not necessarily "wrong" in principle. > > -- 1:1 > CREATE TABLE tbl2 (x INT NOT NULL REFERENCES tbl1 (x) UNIQUE, ...); > > -- > David Portas > > the diagram but I can't create it in the diagram itself interesting point about whether I mean 1:1 or 1:{0/1}. I guess in practice I always have to insert into one of the tables first, so a true 1:1 relationship is impractical (or do the constraints only get checked on commit?) Andy "Andy Fish" <ajf***@blueyonder.co.uk> wrote in message I don't use VS for schema design so I don't know the answer I'm afraid. At news:%23vrmf9UHIHA.5160@TK2MSFTNGP05.phx.gbl... > > > ah thanks - I guess this means I can create it in SQL and it will show up > on the diagram but I can't create it in the diagram itself > least I expect it will show both foreign and candidate keys. > interesting point about whether I mean 1:1 or 1:{0/1}. I guess in practice If it is truly a one-to-one relarionship then why don't you use a single > I always have to insert into one of the tables first, so a true 1:1 > relationship is impractical (or do the constraints only get checked on > commit?) > table instead of two? I'm interested if you have a good reason for this. SQL Server constraints get checked at the end of each statement, not on commit. Some other DBMSs have a feature called deferrable constraints which get checked on commit, but SQL Server doesn't support deferrable constraints. You may be able to "fake" the deferrable behaviour to some extent by inserting a default value or a null for the foreign key and then updating it after the insert to the other table. -- David Portas "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message well - in truth I guess it is a 1:{0/1}. basically for each user I want to news:%23Fi0ssVHIHA.2480@TK2MSFTNGP05.phx.gbl... >> > > If it is truly a one-to-one relarionship then why don't you use a single > table instead of two? I'm interested if you have a good reason for this. > store the name and internal user id etc in one table, but I don't want to put passwords, phone numbers etc in the same table because that might come from a directory server or some external system, so I created a separate table alongside it which will only contain a row if that user information is stored natively in our system rather than an external directory. I am producing the database diagram to illustrate particular aspects of the schema, and for the purposes of the diagram there is a 1:1 relationship which is why I wanted to get that into the diagram. hence, my comments about how to implement a true 1:1 relationship were academic as they don't occur in my case. Show quote > SQL Server constraints get checked at the end of each statement, not on > commit. Some other DBMSs have a feature called deferrable constraints > which get checked on commit, but SQL Server doesn't support deferrable > constraints. > > You may be able to "fake" the deferrable behaviour to some extent by > inserting a default value or a null for the foreign key and then updating > it after the insert to the other table. > > -- > David Portas > > Incidentally I am not using foreign key/constraint checking on the database >> SQL Server constraints get checked at the end of each statement, not on >> commit. Some other DBMSs have a feature called deferrable constraints >> which get checked on commit, but SQL Server doesn't support deferrable >> constraints. >> itself. the last time I used them was on Oracle 7 in around 1994. adding in the foreign key checks caused the performance to grind to a halt and introduced loads of deadlock problems on a relatively lightly loaded system, so we just did all the reference checks with separate SQL statements instead. I would be interested to see if people use SQL server constraints and key checks on production enterprise applications nowadays and what the performance implications are Show quote
"Andy Fish" <ajf***@blueyonder.co.uk> wrote in message Absolutely, yes we do use foreign key constraints in SQL Server. You aren't news:On0PRBWHIHA.4584@TK2MSFTNGP03.phx.gbl... > > >>> SQL Server constraints get checked at the end of each statement, not on >>> commit. Some other DBMSs have a feature called deferrable constraints >>> which get checked on commit, but SQL Server doesn't support deferrable >>> constraints. >>> > > Incidentally I am not using foreign key/constraint checking on the > database itself. > > the last time I used them was on Oracle 7 in around 1994. adding in the > foreign key checks caused the performance to grind to a halt and > introduced loads of deadlock problems on a relatively lightly loaded > system, so we just did all the reference checks with separate SQL > statements instead. > > I would be interested to see if people use SQL server constraints and key > checks on production enterprise applications nowadays and what the > performance implications are > > going to improve performance by adding an extra query to check the reference because it still has to hit the same tables and indexes. Plus the optimizer won't be able to take advantage of a constraint that's implemented in code like it can for a foreign key constraint. Remember that a foreign key doesn't automatically create an index though so you should consider creating indexes for your foreign keys, especially if you plan to do deletes, updates on the parent table. The only reason I can think of to do referential integrity in code is if you need to implement some additional logic or perhaps to disable the constraint selectively during bulk loads. In those cases I would use a trigger or selectively use the NOCHECK option for FK constraints. But 9 times out 10 a foreign key constraint is the best way to enforce referential integrity. -- David Portas Show quote
"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message thanks that's good to know.news:OK7ymrWHIHA.4196@TK2MSFTNGP04.phx.gbl... > "Andy Fish" <ajf***@blueyonder.co.uk> wrote in message > news:On0PRBWHIHA.4584@TK2MSFTNGP03.phx.gbl... >> > >>>> SQL Server constraints get checked at the end of each statement, not on >>>> commit. Some other DBMSs have a feature called deferrable constraints >>>> which get checked on commit, but SQL Server doesn't support deferrable >>>> constraints. >>>> >> >> Incidentally I am not using foreign key/constraint checking on the >> database itself. >> >> the last time I used them was on Oracle 7 in around 1994. adding in the >> foreign key checks caused the performance to grind to a halt and >> introduced loads of deadlock problems on a relatively lightly loaded >> system, so we just did all the reference checks with separate SQL >> statements instead. >> >> I would be interested to see if people use SQL server constraints and key >> checks on production enterprise applications nowadays and what the >> performance implications are >> >> > > Absolutely, yes we do use foreign key constraints in SQL Server. You > aren't going to improve performance by adding an extra query to check the > reference because it still has to hit the same tables and indexes. Plus > the optimizer won't be able to take advantage of a constraint that's > implemented in code like it can for a foreign key constraint. Remember > that a foreign key doesn't automatically create an index though so you > should consider creating indexes for your foreign keys, especially if you > plan to do deletes, updates on the parent table. > > The only reason I can think of to do referential integrity in code is if > you need to implement some additional logic or perhaps to disable the > constraint selectively during bulk loads. In those cases I would use a > trigger or selectively use the NOCHECK option for FK constraints. But 9 > times out 10 a foreign key constraint is the best way to enforce > referential integrity. > there is one other reason not to use foreign key constraints. my application supports oracle, mssql, mysql, and (at a push) ms access for the database tier. Andy Show quote > -- > David Portas > > "Andy Fish" <ajf***@blueyonder.co.uk> wrote in message I don't see why that follows. All of those products support foreign keys and news:O0OzjSXHIHA.700@TK2MSFTNGP05.phx.gbl... > > there is one other reason not to use foreign key constraints. my > application supports oracle, mssql, mysql, and (at a push) ms access for > the database tier. > in most cases a foreign key constraint should be the most efficient method of implementing referential integrity. That's what it's there for and that's what the DBMS is designed to support. I really hope your aversion to such a key feature isn't based only on one bad experience from 13 years ago! That would be a pity. -- David Portas Show quote
"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message thanks for the advice - you're right. I've been burned too many times by news:7YOdnQ4sRKLGPbbanZ2dnUVZ8s2mnZ2d@giganews.com... > "Andy Fish" <ajf***@blueyonder.co.uk> wrote in message > news:O0OzjSXHIHA.700@TK2MSFTNGP05.phx.gbl... >> >> there is one other reason not to use foreign key constraints. my >> application supports oracle, mssql, mysql, and (at a push) ms access for >> the database tier. >> > > I don't see why that follows. All of those products support foreign keys > and in most cases a foreign key constraint should be the most efficient > method of implementing referential integrity. That's what it's there for > and that's what the DBMS is designed to support. I really hope your > aversion to such a key feature isn't based only on one bad experience from > 13 years ago! That would be a pity. > high powered features which seem to save a lot of work but end up being non-portable and unreliable, so my temptation is always to play it safe and that has paid off handsomly over the years. but of course the flip side is that sometimes it's too easy to get stuck into the "because we've always done it that way' mindset. I'll definitely look at integrity constraints for my next project Show quote > -- > David Portas > > Andy Fish (ajf***@blueyonder.co.uk) writes:
> Incidentally I am not using foreign key/constraint checking on the There has been a lot of progress in the worlds of RDBMSs since 1994.> database itself. > > the last time I used them was on Oracle 7 in around 1994. adding in the > foreign key checks caused the performance to grind to a halt and > introduced loads of deadlock problems on a relatively lightly loaded > system, so we just did all the reference checks with separate SQL > statements instead. > > I would be interested to see if people use SQL server constraints and key > checks on production enterprise applications nowadays and what the > performance implications are In my experience, the cost of foreign keys is light, and it would be difficult to beat by rolling your own. Of course, if you don't index your FK columns you may get problems, but that applies no matter you roll your own or you use DRI. Add to that that DRI is so much easier to use. Rolling your own is error- prone and can lead to errors. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics