Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2012
    Posts
    14

    Unanswered: Why I need an P_K in my table,and which column?

    I wonder why I need a P_K assigned to any column,and If it is that important than which column to choose from?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    how are you going to be able to identify one record from another? Is this summer school wrapping up or fall semester starting?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you don't declare a particular key as a primary key, then any other key may attempt to become the primary key by staging a "primary key conflict".

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yeah when that natural versus surrogate rubbish starts up, I always just turn my computer.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2012
    Posts
    14
    If I add a column in each of my tables ...let's say ID with int data type....and make each ID column P_K in each table.Would they still clash with each other?

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Yes and no.

    Suppose you have 3 tables: tableA, tableB, TableC. All with a PK column id
    Code:
    CREATE TABLE tableA (
    	id	INT	NOT NULL	IDENTITY(1, 1),
    	name		VARCHAR(10),
    	CONSTRAINT pk_tableA PRIMARY KEY (id)
    )
    
    CREATE TABLE tableB (
    	id	INT	NOT NULL	IDENTITY(1, 1),
    	name		VARCHAR(10),
    	CONSTRAINT pk_tableB PRIMARY KEY (id)
    )
    
    CREATE TABLE tableC (
    	id	INT	NOT NULL	IDENTITY(1, 1),
    	tableA_id	INT,
    	tableB_id	INT,
    	CONSTRAINT pk_tableC PRIMARY KEY (id),
    	CONSTRAINT fk_tableC_tableA FOREIGN KEY (tableA_id) REFERENCES TableA(Id),
    	CONSTRAINT fk_tableC_tableB FOREIGN KEY (tableB_id) REFERENCES TableB(Id)
    )
    All tables will have their id start from 1, 2, 3, 4, ... So if you only look at the column "id", it may possible have multiple times a value 1: once in tableA, once in tableB and once in tableC. From this you may conclude that they "clash".

    But you never look at a column without it's context, namely the table it belongs to.
    In tableA, the column id can only once have the value 1, and only once the value 2, ...
    In tableB, the column id can only once have the value 1, and only once the value 2, ...

    TableC references to tableA through its foreign key column tableA_id. Suppose tableC.id = 100 and tableC.tableA_id = 2. Now suppose both in tableA and in tableB there is a record with id = 2:
    TableA: id = 2, name = 'cat'
    TableB: id = 2, name = 'Siberia'

    Your question boils down to: how can the database tell, it's the "cat" that's associated with record tableC.id = 100 and not "Siberia".

    This is how you get the associated data with record tableC.id = 100
    Code:
    SELECT C.id, C.tableA_id, A.name
    FROM tableC AS C
    	INNER JOIN tableA AS A ON 
    		C.tableA_id = A.id
    WHERE C.id = 100
    It will return: 100 2 cat

    If the one who wrote the SQL statement made an error, and joined the wrong table, you will of coarse get a wrong result:
    Code:
    SELECT C.id, C.tableA_id, A.name
    FROM tableC AS C
    	INNER JOIN tableB AS A ON
    		C.tableA_id = A.id
    WHERE C.id = 100
    It will return: 100 2 Siberia

    A number of tools will automatically create the correct SQL statement, based on the presence of FOREIGN KEY constraints, when you select different tables in the tool. So tableC.tableA_id will be automatically joined to tableA.id and not to tableB.id.
    Last edited by Wim; 08-12-14 at 12:26.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Nov 2012
    Posts
    14
    Quote Originally Posted by Wim View Post
    Yes and no.

    Suppose you have 3 tables: tableA, tableB, TableC. All with a PK column id
    [CODE]CREATE TABLE tableA (
    id INT NOT NULL IDENTITY(1, 1),
    name VARCHAR(10),
    CONSTRAINT pk_tableA PRIMARY KEY (id)
    )

    CREATE TABLE tableB (
    id INT NOT NULL IDENTITY(1, 1),
    name VARCHAR(10),
    CONSTRAINT pk_tableB PRIMARY KEY (id)
    )

    CREATE TABLE tableC (
    id INT NOT NULL IDENTITY(1, 1),
    tableA_id INT,
    tableB_id INT,
    CONSTRAINT pk_tableC PRIMARY KEY (id),
    CONSTRAINT fk_tableC_tableA FOREIGN KEY (tableA_id) REFERENCES TableA(Id),
    CONSTRAINT fk_tableC_tableB FOREIGN KEY (tableB_id) REFERENCES TableB(Id)
    )
    To me table c was a little confusing ,why you added tableA_id and tableB_id into it?,If it has to make fk then here is what I have done.

    First let's take a look at my query
    Code:
    SELECT * FROM Emp_company
      select * from Employee_Details
      select Emp_company.Companyname,Emp_company.Designation,Employee_Details.Firstname,Employee_Details.Fathername
      from Emp_company,Employee_Details
      where Emp_company.Newid = Employee_Details.Newid
    I have two tables
    1.Employee_Details
    2.Emp_company.
    I have two fields in common(Newid) in both the tables pk_Newid in Employee_Details and fk_Newid in Emp_company
    Here is the screenshot attached(sql_query.jpg) for the result I got.

    Now the problem is when I try to insert any new records into the Emp_company table ,then I got the error

    Code:
    Msg 547, Level 16, State 0, Line 3
    The INSERT statement conflicted with the FOREIGN KEY constraint "fk_Emp_company_Employee_Details". The conflict occurred in database "Pay_V1", table "dbo.Employee_Details", column 'Newid'.
    The statement has been terminated.
    Why cannot I be able to insert records into the table with fk?.
    Attached Thumbnails Attached Thumbnails sql_query.jpg  
    Last edited by chdboy; 08-13-14 at 08:28.

  8. #8
    Join Date
    Nov 2012
    Posts
    14
    I think I was missing the Inner join.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Don't use "NewId" as the name for the PK. Use "id", "Id" or "ID" instead. NewId() is a function that returns a new GUID.

    The use of NewId will sooner or later confuse people (including yourself).
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you show us the CREATE scripts of the tables, PK and FK constraints, .. and the INSERT statements to populate them?

    And populate your tables so that:
    • samcompany has two employees Sam and Sometim.
    • tomcompany has only one employee, Tom.

    Hint: your current data model does not allow this. You will have to tweak it.

    Write SQL statements that

    - give the companyname and the Firstname of all it's employees:
    Expected result:
    samcompany Sam
    samcompany Sometim
    tomcompany Tom

    - give the companyname and the number of employees it employs
    Expected result:
    samcompany 2
    tomcompany 1

    When you get stuck, have a second look at my previous example.
    To make things less theoretical, replace
    "TableA" by "Emp_company"
    "TableB" by "Employee_Details".
    "TableC" by "Company_Employees".

    Use Search-and-replace (Ctrl-H), so "TableA_id" also gets translated into "Emp_company_id" (same with TableB_id).

    Try to do the exercises I gave you with those tables.


    If you got this far:
    Add an Employee_Details record with Firstname = "Chdboy".
    Now this kid is very active, and he works as a DBA at both companies.
    Populate your tables so that:
    • samcompany has three employees Sam, Sometim and Chdboy.
    • tomcompany has two employees Tom and Chdboy.


    When you get stuck, show us what you got so far, describe the problems you encountered and we'll help you further.
    Last edited by Wim; 08-14-14 at 07:00.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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