Is Primary key necesary in the Detail Table
-
Hi Guys
Supposing I am having a Sales Table
So there are 2 table
One the Master Table
That will contain the primary key, bill no, Customer Id ...
And the Details that contain the Details of the Products sold. this table obviously will contain a foreign key of the Master Table.
Now is there a necessity to include a primary key in the details table.
Please give your Comments
-
Every table should always have a primary key. If it does not it is not even a relational table, which defeats the whole purpose of using an RDBMS for managing your data.
-
True
But I am getting opions such as in case of the Sales Table
The Sales Mater Key and the Product Id will act as a Composite key to identify a Unique Record
Copuld you please give more details supporting the necessity for a primary key
-
I could be getting mixed up with Clustered Indexes here but I thought a primary key had an affect on the maintenance of other indices. something to do with their internal calculations.
Why not have a composite primary key instead of your unique index. Presumably you can't have a NULL sales master id or product id in your sales detail table.
-
Then of course those two columns should be the primary key. I guess you have confused primary keys with surrogate keys.
-
David, you did get them mixed up.
The clustered index key is stored with other index keys (in non-clustered indexes). A primary key can be either clustered or non-clustered.
-
Nah, this is SQL Server's questionable feature to create the Primary Key clustered unless you explicitely state otherwise. While I think this is better than not having a clustered index at all, in most cases there are better candidates for the clustered index
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Oh Oh. I Think I got confused. Isn't primary ket just one field.
OK there is composite key. i.e two fields will act as a primary key
then how do i perform joins.
waht i do is I introduce a new Field in every table that atcs a primary key.
Am i wrong ?
-
A PRIMARY KEY is a logical construct that is created from one or more columns of your table. You would join on multiple columns like this:
SET NOCOUNT ON
CREATE TABLE master
(
m1 INT
, m2 INT
CONSTRAINT pk_master PRIMARY KEY(m1,m2)
)
CREATE TABLE child
(
id INT IDENTITY(10,1)
, c1 INT
, c2 INT
)
INSERT INTO master VALUES (1,1)
INSERT INTO master VALUES (2,2)
INSERT INTO child VALUES (1,1)
INSERT INTO child VALUES (1,1)
SET NOCOUNT OFF
SELECT m.*, c.*
FROM master m
INNER JOIN child c
ON m.m1 = c.c1
AND m.m2 = c.c2
DROP TABLE master, child
m1 m2 id c1 c2
----------- ----------- ----------- ----------- -----------
1 1 10 1 1
1 1 11 1 1
(2 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Btw, normally you would give columns containing the same data the same name throughout the database. Forgot this in my example.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
>waht i do is I introduce a new Field in every table that atcs a primary key.
>Am i wrong ?
Yes, like I said you have confused primary keys with surrogate keys. Surrogate keys are used when there is no natural key to use for primary key in a table, which many would consider an incorrect design of the data from the start.
-
" SELECT m.*, c.*
FROM master m
INNER JOIN child c
ON m.m1 = c.c1
AND m.m2 = c.c2 "
is there any thing wrong or less professional in Coding as
ELECT m.*, c.*
FROM master m, child c
where m.m1 = c.c1
AND m.m2 = c.c2
-
No, both yield the same result. My version is the ANSI way of doing JOINs, and according to MS preferable to yours.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The bottom line is simple. Always set a primary key on every table. It can be a single field, or a combination of fields, but in either case it should be unique, and non-null for each record in the table.
A lot of things work much better with a primary key on the table, such as updatable joins in ADO.
Whether or not to use a special field such as GUID or a naturally occuring, never changing piece of the data (which is often hard to find) is another discussion entirely and leads folks into arguments with a religious ferver to them. I'll just say this much: If you're planning on implementing replication, you might as well add the extra field and make it a GUID because you'll have to have it anyway.
Student of SQL and Golf, Master of Neither -
A Primary Key is required to UPDATE data. So, unless you don't wish to ever update the detail you will need a primary key. I frequently create an ID field as an IDENTITY field and mark it as the primary key and then set up a unique key as the clustered index which is how I will primarily access the data in the table. YES, I know there are other ways, but this has proven successful because it provides the primary key for the system and also prevents you for inserting a row which would not be unique.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply