Adding Primary Key To A Table Which Has Already A Primary Key
Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.
Thanks,
Jeyam
View Complete Forum Thread with Replies
Related Forum Messages:
Adding Primary Key
I have some tables that I need to publish, but I can't because they don't contain a primary key. The tables are Null from creation and the null value can't be changed. I was told that I could add a primary key by dropping the index and adding the primary key, but this don't seem to work. Please help.
View Replies !
Adding Primary Keys & Losing Triggers???
Dear Friends, >When you alter your table structure by adding/removing columns, >the table >is actually re-created and the new id is mapped for >all the dependencies. The problem I am having is when I add an additional Primary Key to an existing column name..NOT a new column,,,I am not changing the structure of my table ,,rather I am just adding a Primary Key(an additional Primary Key)..when I do this ...then I try to exit by saving this new information, I receive a Save Attention message telling me that "The following tables will be saved to your database,,do you wish to continue?" If I choose YES ,,then it saves the Table with the newly added Primary Key and consequently wipes/deletes my Triggers,,HOWEVER , if I choose to not save, it just keeps the Table as is and does not add the new PK's to the table. I tried what someone had mentioned by getting out of EM then returning although I think that we are NOT talking about the same problem,,,I am changing the structure of the Primary keys in my table and by doing so, I am subsequently wiping/deleting the triggers that I have wriiten of rthat Table.. Any ideas ...please help me ...more detail? Thanks in advance, Brad Isaacs Junior VB Developer / SQL Server 7.0 databases
View Replies !
How To Auto-increment Primary Key When Adding A New Row Using Update Method?
Hi guys,I followed the ASP.net official tutorial to create a DAL & Business Logic Layer (http://www.asp.net/learn/dataaccess/tutorial02cs.aspx). I have a table with a int ID field. I wish to write a function to add a new entry into this table but have the ID field auto-increment.The ID field is set as the Identity Column and has a Identity Increment & Seed of "1". If I manually go to the table and insert a new record leaving the ID value null it automatically increments. But if I create a C# function to add a new entry I get an error saying that the ID field can't be Null. Is there any way to use the Update method as shown on line 14 below to add a new entry but with it automatically incrementing? I did create a function called InsertDevice that simply inserts the other fields using a SQL INSERT and it auto-increments fine, just wondering if there is a way to do it using the DataTable and the Update method? Thanks for any help!!! 1 public bool AddDevice(string make, string model) 2 { 3 //cannot have the same device entered twice! 4 if (Adapter.FillDeviceCountByMakeModel(make, model) == 1) 5 return false; 6 7 RepositoryDataSet.DevicesDataTable devices = new RepositoryDataSet.DevicesDataTable(); 8 RepositoryDataSet.DevicesRow device = devices.NewDevicesRow(); 9 10 device.make = make; 11 device.model = model; 12 13 devices.AddDevicesRow(device); << Error thrown Here! 14 int rows_affected = Adapter.Update(devices); 15 16 return rows_affected == 1; 17 }
View Replies !
Convert Composite Primary Key Into Simple Primary Key
Uma writes "Hi Dear, I have A Table , Which Primary key consists of 6 columns. total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist. may i convert Composite Primary key into simple primary key in thr table like this. Thanks, Uma"
View Replies !
Auto Incremented Integer Primary Keys Vs Varchar Primary Keys
Hi, I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key. For example: id [unique integer auto incremented primary key - not null], ClientCode [unique index varchar - not null], name [varchar null], surname [varchar null] isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime. Regards Mike
View Replies !
What Happens If A Table Has No Primary Key?
I ran into a table that is used a lot. Well less than100,000 records. Maybe not a lot of records but i believethis table is used often. The table has 26 fields, 9 indexesbut no Primary Key at all!There are no table relationships defined in this database, noNatural keys, only Surrogate keys in the database.1- Maybe an odd question but is it normal to have 1/3 of thetable's fields as indexes? Is this a valid question or it reallydoesn't matter if you have 9 indexes if they are appropriate to beindexes?2- Below is the DDL of the indexes (Is DDL the appropriate termto describe the indexes?) Without going into too technical aboutwhat 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 seethe Primary key icon. Yet here i see the words "PRIMARY KEYNONCLUSTERED". Does this mean UNIQUENO is actually some typeof primary key? If it was CLUSTERED then SQL EM would showUNIQUEID with a key to the left it identifying it as a PK?If that is the case, then what is the difference betweenPRIMARY KEY NONCLUSTEREDandPRIMARY KEY CLUSTERED?CREATE UNIQUE CLUSTERED INDEX [TBLTEST_PK] ON[dbo].[TBLTEST]([UNIQUENO]) WITH FILLFACTOR = 80 ON [PRIMARY]GOCONSTRAINT [PK_TBLTEST] PRIMARY KEY NONCLUSTERED([UNIQUENO]) ON [PRIMARY]GOCREATE UNIQUE INDEX [ASSIGNUNIQUENAME] ON[dbo].[TBLTEST]([USERNO], [STARTDATE], [NAME]) WITHFILLFACTOR = 80 ON [PRIMARY]GOCREATE INDEX [ENDDATE] ON [dbo].[TBLTEST]([ENDDATE]) WITH FILLFACTOR = 80 ON [PRIMARY]GOCREATE UNIQUE INDEX [IUSERASSIGNACT] ON[dbo].[TBLTEST]([USERNO], [TASKNO], [PROCESSENTRYNO])WITH FILLFACTOR = 80 ON [PRIMARY]GOCREATE INDEX [STARTDATE] ON [dbo].[TBLTEST]([STARTDATE]) WITH FILLFACTOR = 80 ON [PRIMARY]GOCREATE INDEX [PROCESSENTRYNOTBLTEST] ON[dbo].[TBLTEST]([PROCESSENTRYNO]) WITHFILLFACTOR = 80 ON [PRIMARY]GO/****** The index created by the following statementis for internal use only. ******//****** It is not a real index but exists asstatistics only. ******/if (@@microsoftversion > 0x07000000 )EXEC ('CREATE STATISTICS [Statistic_NAME] ON[dbo].[TBLTEST] ([NAME]) ')GOCREATE INDEX [TASKNO_IDX] ON [dbo].[TBLTEST]([TASKNO]) WITH FILLFACTOR = 80 ON [PRIMARY]GOCREATE INDEX [TBLTEST_ORGANIZATIONNO_IDX] ON[dbo].[TBLTEST]([ORGANIZATIONNO]) WITHFILLFACTOR = 80 ON [PRIMARY]GOCREATE INDEX [TASKNOUSERNO] ON [dbo].[TBLTEST]([USERNO], [TASKNO]) WITH FILLFACTOR = 80 ON [PRIMARY]GOThank you
View Replies !
Primary Key Of Table
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 Replies !
Finding The Primary Key Of A Table.
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 Replies !
How To Get Primary Key (Columns) Of A Table?
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 Replies !
How To Add Primary Key In Existing Table
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 Replies !
Search The Primary Key Given The Table Name
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 Replies !
Primary Key Columns In A Table
Hi! How can I find all columns in a table that are part of the primary key of that table? SELECT so.Name, sc.* FROM syscolumns sc LEFT OUTER JOIN sysobjects so ON sc.Id = so.Id WHERE so.Name = 'TABLENAME' Gives me all the columns in a table. There is also a "type" column. Is there a specific type that tells me where this colum is part of the PK? This is Sql Server 7 on Win2k. Any idea? Thanks, Helmut
View Replies !
Find Primary Key On Table.
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 Replies !
MS SQL Table Primary Key Increment
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 Replies !
Change The Primary Key Of A Table
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 Replies !
Set Name Of Primary Key In The CREATE TABLE
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 Replies !
Selecting Primary Key Value Into Another Table
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 Replies !
How To Knw Which Column Is Primary Key In A Table
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 Replies !
Creating Table With A Primary Key
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 Replies !
4 Key Primary Key Vs 1 Key 'artificial' Primary Key
Hi all I have the following table CREATE TABLE [dbo].[property_instance] ( [property_instance_id] [int] IDENTITY (1, 1) NOT NULL , [application_id] [int] NOT NULL , [owner_id] [nvarchar] (100) NOT NULL , [property_id] [int] NOT NULL , [owner_type_id] [int] NOT NULL , [property_value] [ntext] NOT NULL , [date_created] [datetime] NOT NULL , [date_modified] [datetime] NULL ) I have created an 'artificial' primary key, property_instance_id. The 'true' primary key is application_id, owner_id, property_id and owner_type_id In this specific instance - property_instance_id will never be a foreign key into another table - queries will generally use application_id, owner_id, property_id and owner_type_id in the WHERE clause when searching for a particular row - Once inserted, none of the application_id, owner_id, property_id or owner_type_id columns will ever be modified I generally like to create artificial primary keys whenever the primary key would otherwise consist of more than 2 columns. What do people think the advantages and disadvantages of each technique are? Do you recommend I go with the existing model, or should I remove the artificial primary key column and just go with a 4 column primary key for this table? Thanks Matt
View Replies !
INSERT Data Into Table That Maybe Have That Primary Key Already
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 Replies !
How To Insert Into A Table With A Uniqueidentifier As Primary Key?
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 Replies !
Creating A Table With A Dual Primary Key
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 Replies !
Table Export Removes Primary Key
When I export a table from my local SQL Server to my web-host's SQLServer, the primary key never seems to export. This happens whether Iuse the "Copy tables and views..." option or the "Copy objects anddata..." option. Anybody know why this happens?
View Replies !
Insert Into Table-Primary Key Error
I'm trying to do multiple insert statements. The table looks likethis:CREATE TABLE $table (CNTY_CNTRY_CD char(3),ST char(2),CNTY_CNTRY_DESCR varchar(50),CNTY_CNTRY_IND char(1),HOME_CNTRY_IND char(1),CONSTRAINT cnty_key PRIMARY KEY (CNTY_CNTRY_CD, ST))I'm using 2 fields for the primary key constraintMy insert statement looks like this:INSERT INTO $table(CNTY_CNTRY_CD,ST,CNTY_CNTRY_DESCR)VALUES(?,?,?)I've been through the list of values and none have both the sameCNTY_CNTRY_CD and ST and yet, this is the error message I'm getting:DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQLServer]Violation of PRIMARY KEY constraint 'cnty_key'. Cannot insert duplicatekey in object 'event_CNTY_CNTRY_CD'. (SQL-23000)[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has beenterminated.. (SQL-01000)(DBD: st_execute/SQLExecute err=-1)Why is it looking for unique in just the one column instead ofreferencing both? What do I need to do to get this to work? Help!
View Replies !
Composite Primary Key On A Table Variable?
Is is possible to create a composite primary key on a table variable?Neither of these two statements are successful:DECLARE @opmcjf TABLE (jobdetailid INT NOT NULL,cjfid INT NOT NULL,cjfvalue VARCHAR(100) NULL)ALTER TABLE @opmcjf ADD CONSTRAINT [PK_opmcjf] PRIMARY KEY CLUSTERED([jobdetailid],[cjfid])andDECLARE @opmcjf TABLE (jobdetailid INT PRIMARY KEY,cjfid INT PRIMARY KEY,cjfvalue VARCHAR(100) NULL)Thanks,Shaun
View Replies !
Create A Table With A Union And Specify Primary Key
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 Replies !
Best Practice: Primary Key In Joing Table
hi there, i have the following joining table (many-to-many relationship)... CREATE TABLE [dbo].[products_to_products_swatch] ( [products_to_products_swatch_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [product_id] [int] NOT NULL , [products_swatch_id] [int] NOT NULL ) ON [PRIMARY] GO question: do i need to include a primary key in this table - being that it is a joing table? thanks mike
View Replies !
Create Table + Index + Primary
for MS SQL 2000 how can I do this in one time (into the CREATE TABLE) CREATE TABLE [dbo].[Users] ( [id_Users] [int] NOT NULL , [Name] [nvarchar] (100) NULL, [Serial] [nvarchar] (100) NULL, ) ON [PRIMARY] ALTER TABLE [dbo].[Users] WITH NOCHECK ADD CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [id_Users] ) ON [PRIMARY] CREATE UNIQUE INDEX [IX_Users] ON [Users]([Serial]) ON [PRIMARY] and that one CREATE TABLE [dbo].[UsersExtra] ( [id_Users] [int] NOT NULL ) ON [PRIMARY] ALTER TABLE [dbo].[UsersExtra] ADD CONSTRAINT [FK_UsersExtra_Users] FOREIGN KEY ( [id_Users] ) REFERENCES [Users] ( [id_Users] ) ON DELETE CASCADE thank you
View Replies !
Multicolumn Primary Key In A Table Variable
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 Replies !
Impact Of Changing Primary Key On Table
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 Replies !
Want To Copy One Entire Row To Same Table Using Primary Key
I have table in my SQL database, I want to copy(or insert) one record(one entire row using primary key, which is auto) I am thinking something like this Insert Into Table1 (a, b, c,d,e) values(select a,b,c,d,e from Table1 where Primarykey=1 or any number) telll me how do I do that maxs
View Replies !
Splitting A Composite Primary Key In A Table
NOTE: I am not interested in any responses that want to argue the use of a unique ID field and autonumbering as the PK. It is quite clear from the forums that this subject is a polarizing one. This question is for those who follow text-book design practices and believe that a composite primary key should be used when it is available. I want to be one of them for the time being. SYNOPSIS: I have three tables, TestSummary, TestDetails, and Steps. The TestSummary table looks like this: Create table TestSummary ( TestSummaryID int identity primary key, ... SequenceID int not null ) It contains the date and time of the test, the serial number, the part number, the test operator's name, and the ID of the sequence of steps used during the test. It uses a unique ID field for the primary key. The TestDetails table looks like this: Create table TestDetails ( TestDetailsID int identity primary key, TestSummaryID int not null, StepID int not null, ... ) It contains the details of the test like voltage readings, current readings, temperature, etc., one record per reading. It also contains the step number of the test sequence specified in the TestSummary table. The Steps table looks like this: Create table Steps ( SequenceID int not null, StepID int not null, Function int not null Primary key (SequenceID, StepID) ) It contains a list of all of the functions to be performed on the device under test by sequence number and the step number within the sequence. When I try to establish a relation between TestSummary.SequenceID and Steps.SequenceID, SQL Server flags an error because TestSummary.SequenceID and Steps.SequenceID:Steps.StepID do not match. What is the problem with this approach?
View Replies !
How To Create Index On Table Variable (Table Don't Have Primary Key)
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 Replies !
Insert Into Relational Table Primary Key From Parent
Hello, I have a Stored Procedure which insterts into Orders table. Orders table is the parent table, with primary key OrdersID. I also have a child table, Client, with foreign key OrdersID. I want it to insert the data into the orders table, and at the same time insert the OrdersID into the FK of the child table. Any info would be appreciated. I have no idea how to do it. My SP is as follows:ALTER PROCEDURE dbo.jobInsert @ClientFileNumber varchar(50),@Identity int OUT ASINSERT Orders(ClientFileNumber, DateTimeReceived) VALUES(@ClientFileNumber, GetDate()) SET @Identity = SCOPE_IDENTITY() RETURN
View Replies !
ADO Table Schema Doesn't Show Primary Key
I'm trying to use the following code to examine table schema for SQL 2000. But when I do, the IsKey value is null or blank. I tried this on two different tables - a State table with a char(2) primary key containing the state abbreviation and a Trend table which has an identity column as the primary key. Public Shared Function GetSchema(ByVal sTable As String) As String Dim sb As New StringBuilder Dim oConn As SqlConnection = OpenConn() Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM " & sTable, oConn) Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly) Dim dt As DataTable = dr.GetSchemaTable() dr.Close() oConn.Close() For ic As Integer = 0 To dt.Columns.Count - 1 sb.Append(ic & ":" & CNull(dt.Columns(ic).ColumnName) & " ") Next sb.Append(vbCrLf) For ir As Integer = 0 To dt.Rows.Count - 1 For ic As Integer = 0 To dt.Columns.Count - 1 sb.Append(ic & ":" & CNull(dt.Rows(ir).Item(ic).ToString) & " ") Next sb.Append(vbCrLf) Next Return sb.ToString End Function Here's the output from the function: ? datahelper.GetSchema("State") "0:ColumnName 1:ColumnOrdinal 2:ColumnSize 3:NumericPrecision 4:NumericScale 5:IsUnique 6:IsKey 7:BaseServerName 8:BaseCatalogName 9:BaseColumnName 10:BaseSchemaName 11:BaseTableName 12:DataType 13:AllowDBNull 14:ProviderType 15:IsAliased 16:IsExpression 17:IsIdentity 18:IsAutoIncrement 19:IsRowVersion 20:IsHidden 21:IsLong 22:IsReadOnly 23:ProviderSpecificDataType 24:DataTypeName 25:XmlSchemaCollectionDatabase 26:XmlSchemaCollectionOwningSchema 27:XmlSchemaCollectionName 28:UdtAssemblyQualifiedName 29:NonVersionedProviderType 0:StateCode 1:0 2:2 3:255 4:255 5:False 6: 7: 8: 9:StateCode 10: 11: 12:System.String 13:False 14:3 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlString 24:char 25: 26: 27: 28: 29:3 0:State 1:1 2:50 3:255 4:255 5:False 6: 7: 8: 9:State 10: 11: 12:System.String 13:False 14:22 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlString 24:varchar 25: 26: 27: 28: 29:22 0:CountryCode 1:2 2:2 3:255 4:255 5:False 6: 7: 8: 9:CountryCode 10: 11: 12:System.String 13:False 14:3 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlString 24:char 25: 26: 27: 28: 29:3 " ? datahelper.GetSchema("Trend") "0:ColumnName 1:ColumnOrdinal 2:ColumnSize 3:NumericPrecision 4:NumericScale 5:IsUnique 6:IsKey 7:BaseServerName 8:BaseCatalogName 9:BaseColumnName 10:BaseSchemaName 11:BaseTableName 12:DataType 13:AllowDBNull 14:ProviderType 15:IsAliased 16:IsExpression 17:IsIdentity 18:IsAutoIncrement 19:IsRowVersion 20:IsHidden 21:IsLong 22:IsReadOnly 23:ProviderSpecificDataType 24:DataTypeName 25:XmlSchemaCollectionDatabase 26:XmlSchemaCollectionOwningSchema 27:XmlSchemaCollectionName 28:UdtAssemblyQualifiedName 29:NonVersionedProviderType 0:TrendID 1:0 2:4 3:10 4:255 5:False 6: 7: 8: 9:TrendID 10: 11: 12:System.Int32 13:False 14:8 15: 16: 17:True 18:True 19:False 20: 21:False 22:True 23:System.Data.SqlTypes.SqlInt32 24:int 25: 26: 27: 28: 29:8 0:Description 1:1 2:50 3:255 4:255 5:False 6: 7: 8: 9:Description 10: 11: 12:System.String 13:False 14:22 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlString 24:varchar 25: 26: 27: 28: 29:22 0:Length 1:2 2:4 3:10 4:255 5:False 6: 7: 8: 9:Length 10: 11: 12:System.Int32 13:False 14:8 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlInt32 24:int 25: 26: 27: 28: 29:8 " Column 6 is IsKey but just displays 6: whereas IsIdentity displays correctly for Column 17. Can someone help me? Thanks in advance.
View Replies !
Problem With Coping Primary Key Into Other Field In The Same Table
is there any way that i can copy primary key(PK) value to another field in the same table .. say my PK is MemberID i want to replicate that into other field say SortID at the same time when the primary key is incrementing. I'm a newbie in this field please pardon me if something is wrong in the way i'm asking ...please help me friends i'm struggling since a long time ... Thanks in advance .. savvy
View Replies !
How To Delete Rows In A Table When No Primary Key Is Defined
Hello,I want to delete duplicate rows in a table when no primary key isdefined.For eg: If we have table1 with data as below,Suma 23 100Suma 23 100I want to delete a row from this table and retain only one row.I tried deleting self joins and exists operator. But it is deletingboth the rows. I want to retain one row.Can anybody help me out.Thanks in advance,Suma--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict221110.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=760520
View Replies !
Table With Duplicate Entry With Primary Keys
We have a SQL Server 6.5 table, with composite Primary Key, having the Duplicate Entry for the Key. I wonder how it got entered there? Now when we are trying to import this table to SQL2K, it's failing with Duplicate row error. Any Help?
View Replies !
Invalid Primary Key Error During Table Linking
Hi, Something strange has happened to my table. I used Enterprise Manager today to delete 3 columns. When I went to re-link the table using Access Linked Table Manager, it gave me an error. I then deleted the link to the table, and tried to Link it again using 'Get External Data---Link Tables'. I am getting an error (no surprise!): " 'dbo.tblSpaceUse.PK_RoomID' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long". When I go into Enterprise Manager to 'manage Indexes' on the table, it shows me that the existing index is in fact dbo.tblSpaceUse.PK_RoomID. About a month ago, I had to rename the index, because it had been pointing to the wrong table. The SQL I used to rename it (in Query Analyzer) is: EXEC sp_rename 'dbo.tblSpaceUse.PK_RoomID', 'tblSpaceUse.PK_RoomID', 'INDEX' I have been using the table successfully since then, until today. I have not done anything with the index; the only change I attempted was to delete 3 columns (not related to the index). I do not think I have made any changes to the table since I renamed the index. I tried to run the rename SQL again (a desperate attempt!) and get the error message: Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 192 Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong. Any ideas on what went wrong and what I can do to fix it??? Thanks, Lori
View Replies !
|
TRACKER
