What Happens If A Table Has No Primary Key?

Jul 23, 2005

I ran into a table that is used a lot. Well less than
100,000 records. Maybe not a lot of records but i believe
this table is used often. The table has 26 fields, 9 indexes
but no Primary Key at all!

There are no table relationships defined in this database, no
Natural keys, only Surrogate keys in the database.

1- Maybe an odd question but is it normal to have 1/3 of the
table's fields as indexes? Is this a valid question or it really
doesn't matter if you have 9 indexes if they are appropriate to be
indexes?

2- Below is the DDL of the indexes (Is DDL the appropriate term
to describe the indexes?) Without going into too technical about
what the table is, what relationships it has with other tables,
would you be able to tell if the indexes are good, bad, too many,
etc?

3- If i open the table in DESIGN view in SQL EM, i don't see
the Primary key icon. Yet here i see the words "PRIMARY KEY
NONCLUSTERED". Does this mean UNIQUENO is actually some type
of primary key? If it was CLUSTERED then SQL EM would show
UNIQUEID with a key to the left it identifying it as a PK?
If that is the case, then what is the difference between
PRIMARY KEY NONCLUSTERED
and
PRIMARY KEY CLUSTERED?


CREATE UNIQUE CLUSTERED INDEX [TBLTEST_PK] ON
[dbo].[TBLTEST]([UNIQUENO]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
CONSTRAINT [PK_TBLTEST] PRIMARY KEY NONCLUSTERED
(
[UNIQUENO]
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [ASSIGNUNIQUENAME] ON
[dbo].[TBLTEST]([USERNO], [STARTDATE], [NAME]) WITH
FILLFACTOR = 80 ON [PRIMARY]
GO

CREATE INDEX [ENDDATE] ON [dbo].[TBLTEST]
([ENDDATE]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IUSERASSIGNACT] ON
[dbo].[TBLTEST]([USERNO], [TASKNO], [PROCESSENTRYNO])
WITH FILLFACTOR = 80 ON [PRIMARY]
GO

CREATE INDEX [STARTDATE] ON [dbo].[TBLTEST]
([STARTDATE]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO

CREATE INDEX [PROCESSENTRYNOTBLTEST] ON
[dbo].[TBLTEST]([PROCESSENTRYNO]) WITH
FILLFACTOR = 80 ON [PRIMARY]
GO

/****** The index created by the following statement
is for internal use only. ******/
/****** It is not a real index but exists as
statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_NAME] ON
[dbo].[TBLTEST] ([NAME]) ')
GO

CREATE INDEX [TASKNO_IDX] ON [dbo].[TBLTEST]
([TASKNO]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO

CREATE INDEX [TBLTEST_ORGANIZATIONNO_IDX] ON
[dbo].[TBLTEST]([ORGANIZATIONNO]) WITH
FILLFACTOR = 80 ON [PRIMARY]
GO

CREATE INDEX [TASKNOUSERNO] ON [dbo].[TBLTEST]
([USERNO], [TASKNO]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO



Thank you

View 4 Replies


ADVERTISEMENT

Adding Primary Key To A Table Which Has Already A Primary Key

Aug 28, 2002

Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.

Thanks,
Jeyam

View 9 Replies View Related

Query Based Off Primary Key Of Parent Table - Adding Child Table

Jan 28, 2012

I need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?

Code:
SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName,
People_tbl.LastName, People_tbl.[Parent ID]
FROM People_tbl RIGHT OUTER JOIN
Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID]
WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)

View 5 Replies View Related

SQL Server Admin 2014 :: Few Record Loss In Table Primary Key Where Same Records Exists In Foreign Key Table?

Jun 21, 2015

Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.

View 3 Replies View Related

How To Create Index On Table Variable (Table Don't Have Primary Key)

Feb 26, 2008



Hi all,


my stored procedure have one table variable (@t_Replenishment_Rpt).I want to create an Index on this table variable.please advise any of them in this loop...
below is my table variable and I need to create 3 indexes on this...


DECLARE @t_Replenishment_Rpt TABLE
(
Item_Nbr varchar(25) NULL,
Item_Desc varchar(255) NULL,
Trx_Date datetime NULL,
Balance int NULL,
Trx_Type char(10) NULL,
Issue_Type char(10) NULL,
Location char(25) NULL,
Min_Stock int NULL,
Order_Qty int NULL,
Unit char(10) NULL,
Issue_Qty int NULL,
Vendor varchar(10) NULL,
WO_Nbr varchar(10) NULL,
Lead_Time int NULL,
PO_Nbr char(10) NULL,
PO_Status char(10) NULL,
Currency char(10) NULL,
Last_Cost money NULL,
Dept_No varchar(20) NULL,
MSDSNbr varchar(10) NULL,
VendorName varchar(50) NULL,
Reviewed varchar(20) NULL
)

I tryed all below senarios...it is giving error...


--Indexing the @t_Replenishment_Rpt table on the column Names Item Number, Vender , Department Number
--EXEC sp_executesql(CREATE UNIQUE CLUSTERED INDEX Replenishment_index ON @t_Replenishment_Rpt (Item_Nbr))
--CREATE UNIQUE CLUSTERED INDEX Idx1 ON @t_Replenishment_Rpt.Item_Nbr
INDEX_COL ( '@t_Replenishment_Rpt' , ind_Replenishment_id , Item_Nbr )
--EXEC sp_executesql('SELECT INDEXPROPERTY('+ '@t_Replenishment_Rpt' + ', ' + 'Item_Nbr' + ',' + 'IsPadIndex' + ')')
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Vendor','IsPadIndex'))
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Dept_No','IsPadIndex'))


View 3 Replies View Related

How Do I Copy / Insert A Primary Key Value Of One Table Into Another Table?

Dec 10, 2007

 

Hi i have set up  two very simple tables, I want a user to be able to create a basic account ( data stored in User_Profile  table with Id set as the Primery Key as Identity) I
want the user to be able to be able to return to their account at a later date
and then post multiple reviews of different bands they have seen at a later date.
I kept the tables in my example very simple so I could get my head
around the concept, but generally, I want to connect the Id (PK) value in
User_Profile table to the User_Id filed in the User_Review table,
so every review that user writes, will be connected directly to their Id.  

Any help you could give would be fantastic a i have no idea where to start!!!

  

User_Profile

Id   int,  ( as primary Identity Key)

Name

City

Country

 

I have a second table called User Reviews

 User_Revews

Revew_Id   int ,  ( as primary Identity Key)

User_Id  int, ( I want this to contain the Id value in
the User profile Table)

Review_Details

 

 

Thanks

 

Odxsigma

View 3 Replies View Related

Columns In Primary Table And Foreign Key Table

Feb 12, 2007

mahesh writes "HI,

I am new to sql server.

can anybody help me

I have a table named tblqualificationmaster.

can i know the foreignkeys and the table related to this

tblqualificationmaster having foeign keys using stored procedure."

View 1 Replies View Related

Table Primary Key(Varchar) Table Question

Jan 17, 2008

Hi,

I have a Users table that I use for membership. I am using username varchar(30) as the primary key for this table since username will always be unique.




The question I have is regarding how SQL Server actually stores data:

I see that when I add users, they are always stored alphabetically sorted on username.

I was expecting that all users will appear on the users table in the order they were added.

Example: I have 3 users (john, jonah, wilson). Now I added 4 user with username='bob'

If I execute select * from users, it returns me (bob, john, jonah, wilson). Look bob has become the first row of the table.


My question: Is Sql server moving 3 older rows to make room for 'bob' and it is also rebuilding part of the index due this new username 'bob'?


If this is the case, then it will have big impact if I have 100K users and I add one user that becomes firstrow. In that case 99,999 rows will have to move.


Bottom line, insert, delete will be very expensive.

I know sql server keeps data physically sorted on PK. But I am concerned here since rows are losing the order in which they were inserted.

Thanks

View 8 Replies View Related

Primary Key Per Table

Dec 2, 2013

There is a restriction that we must have only one primary key per table. We can have composite primary key by defining primary key over 2 or more columns. Actually,, a primary key uniquely defines the table and if we have two or more columns which follow unique and not null features why can't we have two primary keys?? I want to know what is the main reason behind having only one primary key in a table??

View 3 Replies View Related

Primary Key Of Table

Mar 21, 2008

Hi to all.

I have a table with about 8 columns as a primary key. I would like to delete one column but not destroy the other 7 primary keys and their relationships between the other tables. Is it possible to do this?

Thanks.

View 5 Replies View Related

Finding The Primary Key Of A Table.

Dec 3, 2007

I'm trying to find the primary key on a given table in SQL Server 2000 using SQL.  I'm querying the sysobjects table to find a given table, and then querying the sysindexes table.  I've ALMOST found what I'm looking for.   I see the indexes and columns etc. on the tables in the database, I just don't see the field that indicates that the index is the primary key.
Can anyone help?
Thanks, Alex

View 6 Replies View Related

The Dropped Table Does Not Have A Primary Key And Cannot Be Used

Feb 25, 2004

I'm trying to drop a table onto the design view in Web Matrix and the following message appears: "dropped table does not have a primary key and cannot be used".

I'm using a SQL 2000 database that was previously an MSDE 2000 database. Is there anyway that I can define a column as a primary key?

Thanks.

View 5 Replies View Related

How To Get Primary Key (Columns) Of A Table?

Jan 5, 2006

I want to get the Primary Key Columns in Arrays by sending a tablename. I am using SQL Server 2000 and I want to make a find utility in VB.net whichwill work for all the forms; I have tables with one Primary key and some tables with composite Primary keys. I used to do this in VB 6 by making a function which fills the Primary Keys inList Box (I require to fill in list box), now I need to get in array. Can some one tell me the migration of the following VB 6 Code? This was written for the MS Access, I need same for SQL Server, I cannot find Table Def and Index Object in VB.net 2003. Public Sub GetFieldsFromDatabase (ldbDatabase As Database, lsTableName AsString)     Dim lttabDef As TableDef  Dim liCounter As Integer  Dim liLoop As Integer  Dim idxLoop As Index  Dim fldLoop As Field
  With ldbDatabase    For Each lttabDef In .TableDefs      If lttabDef.Name = lsTableName Then        liCounter = lttabDef.Fields.Count        For liLoop = 0 To liCounter - 1          cboFieldLists.List(liLoop) = lttabDef.Fields(liLoop).Name        Next liLoop        For Each idxLoop In lttabDef.Indexes          With idxLoop            lblIndexName = .Name            If .Primary Then              liCounter = 0              For Each fldLoop In .Fields               cboPrimaryKeys.List(liCounter) = fldLoop.Name                liCounter = liCounter + 1              Next fldLoop            End If          End With        Next        cboFieldLists.ListIndex = 0        If cboPrimaryKeys.ListCount > 0 Then          cboPrimaryKeys.ListIndex = 0        End If        Exit For      End If     Next End WithEnd Sub

View 2 Replies View Related

Find Primary Key On Table.

Feb 26, 2008

I need to find the primary key of a table, in MySQL i used SHOW COLUMNS and looped through them to find which one was primary if any. The MSSQL equivalent is SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'table_name' apparently. However the result doesnt give me any key information. How can i find out
1. if a primary key exists on a table
2. what column that primary key exists on

View 2 Replies View Related

MS SQL Table Primary Key Increment

May 8, 2008

Hi Guys,

I have designed a simple table named "test" with ID as primary key and Name as a string data. When I delete a row from the table and insert a new row.. then the ID column increments itself by 1..
for eg : if i have 2 rows in my table

1 Karthik
2 you

if I delete the 2nd row and insert your name in the place of 2nd row..
actually my rows shows

1 Karthik
3 yourname

could anybody give me some advices..

Thanks,
Karthik Gopal

View 1 Replies View Related

One Primary Key To Two Column In Same Table

Feb 9, 2015

I have a table name Dispatch with the following column
ID,Date, Name, Pickup, Going

I have a next table called Move with the following column
ID, MoveName

I link Move table with Dispatch table via relationship.
ID_Pickup, ID_Going

upon running query no data is shown unless i remove one of the column form the query either pickup or going ...

View 7 Replies View Related

Creating Table With A Primary Key

Jun 29, 2006

Hi,

I need to create a new table in our database.
This table is not linked into the existing schema in anyway, so i'm not sure if I need a primary key or not.
either way, coudl anyone tell me how to create a primary key ni the CREATE TABLE statement.
I have tried searching but cannot find the answer.

many thanks,
Matt

View 7 Replies View Related

Update A Table With Primary Key

May 1, 2007

i have 2 tables. Table a and Table b. i need to insert only the new records from Table a to Table B

how do you update table B where the primary key is 4 columns.

solved.!

View 5 Replies View Related

How To Add Primary Key In Existing Table

Aug 17, 2006

i have table fff .it has two fields one is fno int , another is fnamevarchar(20)ffffno fname-------- -----------100 suresh102 rameshhere there is no not null constraint and identity column theni am add primary key constraint fno column pls help me

View 1 Replies View Related

Search The Primary Key Given The Table Name

Jul 20, 2005

Hi all,How can get the primary key string from the given table name? i knowit should from system tables of "sysobjects, syscolumns, andsysconstraints", but when i execute the statement like that:select a.name from syscolumns a,sysobjects b,sysconstraints cwhere a.id = b.id and b.name ='Agreement' and a.id = c.id and a.colid= c.colid and c.status = 1i can't get the primary key out, what the trick here? bye the sql helpfile,'status' in sysconstraints table:1 = PRIMARY KEY constraint.2 = UNIQUE KEY constraint.what is exact value refers to PRIMARY KEY constraint?thanks,Robert

View 1 Replies View Related

Selecting Primary Key Value Into Another Table

Apr 11, 2008

I have table A with Primarykey column, AId, Identity field.

I have table B with foriegn key column , AId,

I have same number of rows in both tables (over million), but in Table B, column AId is null at present, as it was added later.

Now I need to select all AId values and update them in existing rows.

Any idea, how would my T-SQL look like???

Many Thanks,

View 9 Replies View Related

SQL To Identify The Primary Key In A Table?

Oct 24, 2007

Hi

I have been looking for a way to identify the primary key defined in a
table using a SQL Sentence...how can i do it?

Thanx for your support!!!

Diego Bayona

View 5 Replies View Related

How To Knw Which Column Is Primary Key In A Table

May 1, 2006

hi all

my question is which query shud i use in sql server 2000 to get which column or columns are primary keys of table

i dont want to use any stored procedures only sql query

sp_primary_keys_rowset is one of d stored proc in sql server 2005 but i couldn't understand which query they are using

i only want to use sql query

View 3 Replies View Related

RDA Tracking - No Primary Key On Table

Apr 18, 2007

Hello,



Can a table be tracked if it does not have a primary key? I am using rda.Pull method in VS2005 using C#. If not, what are my options? Any help is appreciated.



Thanks.

View 1 Replies View Related

Change The Primary Key Of A Table

May 12, 2008

Hi,

I have a table named "MOTIVE", in this table the primary key is a colum named "MOTIVE_ID". I want to give primary key status to another column named "MOTIVE_CODE" instead. What are the correct statements to drop the primary key from a column and assign it to another.

Thanks for your help.

View 17 Replies View Related

Set Name Of Primary Key In The CREATE TABLE

Apr 28, 2008

I think there has to be a way to do this but I'm not seeing it.

I would like to set the names of my primary keys in the CREATE TABLE statements. I like this for documentation so it's very clear what the PK name is. When the system generates the key names, it always add the number suffix at the end. I would need to do this both when the PK is a single column and when it is multiple columsn (see examples below).

Thanks very much for your assistance.


CREATE TABLE dbo.SecAppRole1 (

app_id INT IDENTITY(1,1),

app_name_field VARCHAR(128) NOT NULL PRIMARY KEY ,

app_role VARCHAR(128) NOT NULL,

app_role_password VARCHAR(50) NOT NULL)


CREATE TABLE dbo.SecUserAppPermission1 (

app_id INT NOT NULL,

windows_user_name VARCHAR(128) NOT NULL,

user_permission CHAR(01) NOT NULL,

PRIMARY KEY CLUSTERED (app_id ASC, windows_user_name ASC))

View 4 Replies View Related

How To Automatically Create New Records In A Foreign Table When Inserting Records In A Primary Table.

Sep 13, 2006

Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables.  I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key.  Is there a way to automatically create new records in the foreign tables that will have the new id?  Would this be a job for a trigger, stored procedure?  I admit I haven't studied up on those yet--I am learning things as I need them. Thanks. 

View 4 Replies View Related

How To Insert Into A Table With A Uniqueidentifier As Primary Key?

Jun 28, 2006

I would like to insert into a table with a primary key that has a uniqueidentifier. I would like it to go up by one each time I execute this insert statement. It would be used as my ReportId
My VB code is this.
Protected Sub btncreate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btncreate.Click
'set connection string
Dim errstr As String = ""
Dim conn = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True")
'set parameters for SP
Dim cmdcommand = New SqlCommand("sprocInsertNewReport", conn)
cmdcommand.commandtype = CommandType.StoredProcedure
cmdcommand.parameters.add("@UserName", Session("UserName"))
cmdcommand.parameters.add("@Week", vbNull)
cmdcommand.parameters.add("@Date", vbDate)
cmdcommand.parameters.add("@StartTime", vbNull)
cmdcommand.parameters.add("@EndTime", vbNull)
cmdcommand.parameters.add("@HeatTicket", vbNull)
cmdcommand.parameters.add("@Description", vbNull)
cmdcommand.parameters.add("@TakenAs", vbNull)
cmdcommand.parameters.add("@Dinner", vbNull)
cmdcommand.parameters.add("@Hours", vbNull)
cmdcommand.parameters.add("@Rate", vbNull)
cmdcommand.parameters.add("@PayPeriod", vbNull)
cmdcommand.parameters.add("@LastSave", vbNull)
cmdcommand.parameters.add("@Submitted", vbNull)
cmdcommand.parameters.add("@Approved", vbNull)
cmdcommand.parameters.add("@PagerDays", vbNull)
cmdcommand.parameters.add("@ReportEnd", vbNull)
Try
'open connection here
conn.Open()
'Execute stored proc
cmdcommand.ExecuteNonQuery()
Catch ex As Exception
errstr = ""
'An exception occured during processing.
'Print message to log file.
errstr = "Exception: " & ex.Message
Finally
'close the connection immediately
conn.Close()
End Try
If errstr = "" Then
Server.Transfer("TimeSheetEntry.aspx")
End If
My SP looks like this
ALTER PROCEDURE sprocInsertNewReport

@UserName nvarchar(256),
@Week Int,
@Date Datetime,
@StartTime Datetime,
@EndTime DateTime,
@HeatTicket int,
@Description nvarchar(max),
@TakenAs nchar(10),
@Dinner Nchar(10),
@Hours Float,
@Rate Float,
@PayPeriod int,
@LastSave Datetime,
@Submitted Datetime,
@Approved DateTime,
@PagerDays int,
@ReportEnd DateTime
AS
INSERT INTO
ReportDetails
(
rpUserName,
rpWeek,
rpDate,
rpStartTime,
rpEndTime,
rpHeatTicket,
rpTicketDescription,
rpTakenAs,
rpDinnerPremium,
rpHours,
rpRate,
rpPayPeriod,
rpLastSaveDate,
rpSubmittedDate,
rpApprovedDate,
rpPagerDays,
rpReportDueDate
)
VALUES
(
@Username,
@Week,
@Date,
@StartTime,
@EndTime,
@HeatTicket,
@Description,
@TakenAs,
@Dinner,
@Hours,
@Rate,
@PayPeriod,
@LastSave,
@Submitted,
@Approved,
@PagerDays,
@ReportEnd
)
RETURN
Any Ideas?
thx!

View 7 Replies View Related

INSERT Data Into Table That Maybe Have That Primary Key Already

May 12, 2007

Hi, I'm not user to inserting data into databases, usually I just read the data.  So I think my problem might be pretty common.I have a table of longitudes, latitudes, city names, and country names.  I set the primary key to be the columns longitude and latitude.   I have a method that generates the user's location and the mentioned data.  So I want to only insert the new data into the database if it is new and unique.  currently if the same user goes to my site, it inserts the data fine the first time and then throws and error the second time because it is inserting duplicate primary key information.  Do I need to query the database to see if the data record already exists?  or is there a way to insert the record only if it is "new"?? Thanks for the help!! 

View 2 Replies View Related

Creating A Table With A Dual Primary Key

Jul 12, 2004

This question may be a little complicated.

I am building a DTS Package that is moving data from our webstore (written in house) to a Warehouse Management System(WMS - Turnkey) and I've encountered a problem. both pieces of software have an orders table and an Ordered_Items table, related by the order_ID (makes sense so far). Here is the problem. The primary key on the webstore's Ordered_Items table is a single column (basically an Identity variable), while the primary key on the WMS's Ordered_Items table is a dual column primary key, between the Order_ID and the Order_LineID, so the data should be stored like:

OrderID Order_LineID
1 1
2 1
2 2
2 3
3 1
3 2
4 1

Get the Idea? So I have to create this new Order_LineID column. How can I accomplish this with a SQL statement?

Thanks!!!!!

View 5 Replies View Related

Impact Of Changing Primary Key On Table

Jul 3, 2001

Hi All,

I want to know what will be the impact of changing the primarykey on a table which already has a lot of data.

For example, column A is unique, primary key. I want to make column B as unique, primary key.

Can I do that? What will be the impact on database performance?

Thanks
Sri

View 1 Replies View Related

Multicolumn Primary Key In A Table Variable

Jan 11, 2005

Is it possible? And if yes what's the correct syntax?

I tried both


Declare @expired TABLE (
JdtID Int PRIMARY KEY,
SiteID Int PRIMARY KEY,
PackageId Int,
PackageControlsExpiration Bit,
IsSlot Bit,
MembershipPoints SmallInt,
SupportsAutopost Bit
);


and


Declare @expired TABLE (
JdtID Int,
SiteID Int,
PackageId Int,
PackageControlsExpiration Bit,
IsSlot Bit,
MembershipPoints SmallInt,
SupportsAutopost Bit,
CONSTRAINT Expired_PK PRIMARY KEY (JdtId, SiteId)
);


and neither works.

Thanks, Jenda

View 1 Replies View Related

Create A Table With A Union And Specify Primary Key

Jul 7, 2004

I want to create a table with a union. Which I have already accomplished. I want to specify the Primary Key in the statement.

Or would I have to use another statement. How would I do that? With an update and what would the syntax be?



Thanks before hand,

itarin

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved