Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Mar 2003
    Posts
    21

    Unanswered: table linking discussion

    I would like to hear your thoughts on a philosophy I adhere to.

    As a rule of thumb I've always preached that Unique Indexes are for linking tables and Primary Keys are used to ensure that records aren't duplicated. Iíve embraced this philosophy for a couple reasons, the main one being that I donít have to create numerous foreign key fields in the foreign key table.

    However Iíve done most of my programming in Access and am now in need of something more robust (SQL Server v7) and Iím wondering if I need to reconsider.

    I do also have a how to question; that being is it possible to create a table join on a unique index in SQL Server v7 and if so how? I would like to have an Auto Number / Auto Incremented / Unique Identifier field in the Primary Key table that links to a numeric field in the Foreign Key table.

    Thanks in advance
    Dog

  2. #2
    Join Date
    Sep 2003
    Posts
    364
    You can also use a unique constraint, but it's ultimately implemented as a unique index. 6 one way 1/2 dozen another.

    Look at the BOL.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: table linking discussion

    Originally posted by dognation
    As a rule of thumb I've always preached that Unique Indexes are for linking tables
    HUH?

    How do you maintain data integrity with a unique Index to it's parental table?

    "Linking" Tables?

    Have you ever looked at the Northwind database for examples?

    Do you have SQL Server Installed Now?

    Here's some sample code out of SQL Servers version of Northwind....

    Code:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Order_Details_Orders]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[Order Details] DROP CONSTRAINT FK_Order_Details_Orders
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Order Details]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Order Details]
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Orders]
    GO
    
    CREATE TABLE [dbo].[Order Details] (
    	[OrderID] [int] NOT NULL ,
    	[ProductID] [int] NOT NULL ,
    	[UnitPrice] [money] NOT NULL ,
    	[Quantity] [smallint] NOT NULL ,
    	[Discount] [real] NOT NULL 
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[Orders] (
    	[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
    	[CustomerID] [nchar] (5) NULL ,
    	[EmployeeID] [int] NULL ,
    	[OrderDate] [datetime] NULL ,
    	[RequiredDate] [datetime] NULL ,
    	[ShippedDate] [datetime] NULL ,
    	[ShipVia] [int] NULL ,
    	[Freight] [money] NULL ,
    	[ShipName] [nvarchar] (40) NULL ,
    	[ShipAddress] [nvarchar] (60) NULL ,
    	[ShipCity] [nvarchar] (15) NULL ,
    	[ShipRegion] [nvarchar] (15) NULL ,
    	[ShipPostalCode] [nvarchar] (10) NULL ,
    	[ShipCountry] [nvarchar] (15) NULL 
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[Order Details] WITH NOCHECK ADD 
    	CONSTRAINT [PK_Order_Details] PRIMARY KEY  CLUSTERED 
    	(
    		[OrderID],
    		[ProductID]
    	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
    GO
    
    ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD 
    	CONSTRAINT [PK_Orders] PRIMARY KEY  CLUSTERED 
    	(
    		[OrderID]
    	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
    GO
    
    ALTER TABLE [dbo].[Order Details] WITH NOCHECK ADD 
    	CONSTRAINT [DF_Order_Details_UnitPrice] DEFAULT (0) FOR [UnitPrice],
    	CONSTRAINT [DF_Order_Details_Quantity] DEFAULT (1) FOR [Quantity],
    	CONSTRAINT [DF_Order_Details_Discount] DEFAULT (0) FOR [Discount],
    	CONSTRAINT [CK_Discount] CHECK ([Discount] >= 0 and [Discount] <= 1),
    	CONSTRAINT [CK_Quantity] CHECK ([Quantity] > 0),
    	CONSTRAINT [CK_UnitPrice] CHECK ([UnitPrice] >= 0)
    GO
    
    ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD 
    	CONSTRAINT [DF_Orders_Freight] DEFAULT (0) FOR [Freight]
    GO
    
     CREATE  INDEX [OrderID] ON [dbo].[Order Details]([OrderID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
    GO
    
     CREATE  INDEX [OrdersOrder_Details] ON [dbo].[Order Details]([OrderID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
    GO
    
     CREATE  INDEX [ProductID] ON [dbo].[Order Details]([ProductID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
    GO
    
     CREATE  INDEX [ProductsOrder_Details] ON [dbo].[Order Details]([ProductID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
    GO
    
     CREATE  INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
    GO
    
     CREATE  INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
    GO
    
     CREATE  INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
    GO
    
     CREATE  INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
    GO
    
     CREATE  INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) WITH  FILLFACTOR = 90 ON [PRIMARY]
    GO
    
     CREATE  INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) WITH  FILLFACTOR = 90 ON [PRIMARY]
    GO
    
     CREATE  INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) WITH  FILLFACTOR = 90 ON [PRIMARY]
    GO
    
     CREATE  INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) WITH  FILLFACTOR = 90 ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[Order Details] ADD 
    	CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY 
    	(
    		[OrderID]
    	) REFERENCES [dbo].[Orders] (
    		[OrderID]
    	),
    	CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY 
    	(
    		[ProductID]
    	) REFERENCES [dbo].[Products] (
    		[ProductID]
    	)
    GO
    
    ALTER TABLE [dbo].[Orders] ADD 
    	CONSTRAINT [FK_Orders_Customers] FOREIGN KEY 
    	(
    		[CustomerID]
    	) REFERENCES [dbo].[Customers] (
    		[CustomerID]
    	),
    	CONSTRAINT [FK_Orders_Employees] FOREIGN KEY 
    	(
    		[EmployeeID]
    	) REFERENCES [dbo].[Employees] (
    		[EmployeeID]
    	),
    	CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY 
    	(
    		[ShipVia]
    	) REFERENCES [dbo].[Shippers] (
    		[ShipperID]
    	)
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by peterlemonjello
    You can also use a unique constraint, but it's ultimately implemented as a unique index. 6 one way 1/2 dozen another.

    Look at the BOL.
    Yeah but how does that establish RI?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2003
    Posts
    21
    Brett,
    You are way beyond my reach here bud. I need a little milk instead of the ribeye you've given me.

    my situation:

    'tblTeacher' table (primary key table) has unique index field called 'RecordID'. The table 'tblTeacher' has as its primary key a single field 'txtSSN' which in my mind handles data integrity (doesn't allow data entry person to create John Doe twice).

    'tblClass' table (foriegn to tblTeacher) has 'numTeacherID' numeric field which is the foreign key field to tblTeacher.RecordID. The table tblClass has as its primary key numClassID, numTeacherID, numYear, numQuarter, numHour fields (doesn't allow data entry person to assign a teacher to the same class , year, quarter, hour).

    I have these two tables linked on the tblTeacher.RecordID and tblClass.numTeacherID. I'm relying on Access to maintain integrity via the 'Enforce Referencial Integrity' checkbox on the link. Which in my mind isn't allowing a new record in the foreign key table to be created without having a matching record in the primary key table. This is my method of ensuring data integrity.

    By the way what is RI?

    Thanks

  6. #6
    Join Date
    Mar 2003
    Posts
    21
    Brett,
    by the way, I'm not going to be writting any db code as you've shown in your response. I have an asp application that I will be using as a frontend to this SQL server database. This asp app connects to an existing Access db that I want to migrate to SQL server. In addition this asp app only issues SELECT, INSERT, UPDATE and DELETE SQL commands to the backend so I just need a good normalized SQL Server version of my Access tables, I'm not using any stored procs, forms, reports ect in the Access file just using the tables.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Re: table linking discussion

    Originally posted by dognation
    I would like to hear your thoughts on a philosophy I adhere to.

    As a rule of thumb I've always preached that Unique Indexes are for linking tables and Primary Keys are used to ensure that records aren't duplicated. Iíve embraced this philosophy for a couple reasons, the main one being that I donít have to create numerous foreign key fields in the foreign key table.
    what you call a unique index is usually called a surrogate key, and what you call a primary key is usually called a natural key

    either surrogate keys or natural keys can be declared as the PK

    do a search either here within this site or out on the web in general for surrogate or natural key

    it is a very sensitive area

    there are pros and cons for each method, and you will find adherents in both camps

    my personal position is mostly in the natural camp but i will use surrogates when i feel it advisable

    be careful if you run into anyone who thinks surrogate keys are a good idea just as a matter of habit or priciple, because chances are, they have never heard of ON UPDATE CASCADE or seen the beauty of not having to join a whole chain of related tables in queries involving composite keys

    your ideas about foreign and primary keys and enforcing integrity will carry over perfectly to sql server

    as for your other question, the equivalent to autonumber in sql server is an IDENTITY column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How do you plan the migration to SQL server?

    The DDL I posted was just simply scripted from SQL servers sample database Northwind.

    It shows you all the examples of what your discussing...indexes, foreign keys, primary kets, and other contraints....


    I'm relying on Access to maintain integrity via the 'Enforce Referencial Integrity' checkbox on the link.
    I got confused with "linking"...which is really establishing foreign keys between tables, which is RI (see above).

    btw the way, your structure looks ok to me...except that you lost me a little on the relationship beteen the teacher and the class...

    pk on teacher is txtSSN...fk in class is numTeacherId? That doesn't make sense...

    Why isn't the fk txtSSN?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by dognation
    I need a little milk instead of the ribeye you've given me.
    I had better stuff that I had to delete....

    As a rule of thumb I've always preached that
    kinda got me going....

    Sorry....

    Anyway, do you have a dba? If not, you're going to need to get your hands dirty...all for the better mind you...very good skill to obtain...

    and also, stored procedures are the way to go....


    Good Luck, and let us know how it's going....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

    Lightbulb Personal Opinion

    The comment on Natural Keys is right on the mark (IMHO). Unless performance of data retrieval dictates that references be integers, I would always go with Natural Keys.

    One thing that jumps out at me is that your naming of referenced items changes from one table to another. This definitely make maintenance a nightmare as changes are made to the data model or the client application.

    I do not abide to your philosophy. For performance reasons (I may be wrong here) I would believe that a Primary Key would be the most efficient manner of defining relationships where applicable.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, my tow cents.

    I think natural keys are fine for simple schemas or cases when the natural key is a single field, but for complex schemas where tables may have multiple relationships to other tables or where the number of fields in the natural key increases with each level in the schema hierarchy.

    My personal preference is to always use a surrogate key (either integer or guid) for every table, because the consistency makes development easier.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    but for complex schemas where tables may have multiple relationships to other tables or where the number of fields in the natural key increases with each level in the schema hierarchy......
    ?? that sentence fragment was left unfinished
    ... always use a surrogate key (either integer or guid) for every table, because the consistency makes development easier
    "consistency is the last refuge of the unimaginative" -- oscar wilde

    dude, you musta missed my comment earlier about compound natural keys and the beauty of simpler queries in complex relationships

    are you honestly suggesting that in complex relationships, surrogate keys improve your ability to write simple queries?

    i can only conclude that you have not given complex relationships enough thought

    and this is borne out by your admission that you assign surrogate keys out of habit


    not to pick on you personally, man, because you have a ton of good ideas in your head, moreso than most people working with sql

    but i just cannot understand people who always use surrogates out of habit
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I didn't USED to assign surrogate keys out of habit. As a matter of fact, I used to use natural keys almost exclusively. It's just that as I got into more complex application logic I found over and over that there were things I could implement with surrogate keys that I could not with natural keys, and I found query design and update cascaded to be overly verbose and complex when the the natural key started extending to more than five or six colums.

    Using a consistent surrogate key (often a GUID) allows me to reuse code not just between databases, but across objects within a database. I also have VB Code and forms that can be resused from application to application because they always know that the unique identifer is a single record of a uniform datatype.

    Long and short of it, I've never run into a problem I couldn't solve because I assigned a surrogate key, but I have run into problems I couldn't solve (easily and efficiently) without them.

    I still use natural keys for simple schemas, and I guess I don't ALWAYS use surrogates because I wouldn't add a surrogate key to a simple lookup list.

    Still, after avoiding them on so many occasions only to find out that they would help solve a particular problem, I now use them as a matter of principle. My applications work, my schemas are robust, and my codes is reusable, so I'm happy.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and Oscar Wilde never reused his code.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good answer



    "I wouldn't add a surrogate key to a simple lookup list'

    heh, that'll get you in a lot of trouble with the "but the lookup code could change!" crowd

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •