hi friends,
for my application i created a table which has a primary key column named num. this table contains this columns only.now i want to remove the column num. when i am tring to remove this column it is throwing the error:
"The object 'PK__Table_Null__023D5A04' is dependent on column 'num'."
so pls guide me on how to delete a primary key column from table.
thanx in advance,
rammohan
I have the need to remove a constraint on a table since I'm trying to alterthe datatype of one of the columns. I know I can drop the constraint giventhe name, but since the name is auto generated (something likeDF__WHRPT_ITV__Expor__45F365D3)I need a way to find this constraint name so that I can programmaticallyremove it.I can get the name using sp_helpconstraint on the table, but can'tseem to locate where the actual constraint_name is stored.This is on SQL 2000.Any help is appreciated.Thanks,-Gary
View 2 Replies View Related Here is my issue I am new to 2005 sql server, and am trying to take my old data which is exported to a txt file and import it to tables in sql. The older database is non relational, and I had made several exports for the way I want to build my tables. I built my packages fine and everything is working until I start building relationships. I remove my foreign key and the table with the primary key will get updated for the package again. I need to update the data daily into sql, and once in it will only be update from the package until the database is moved over.
It will run and update with a primary key until I add a foreign key to another database.
Here is my error when running the package when table 2 has a foreign key.
[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [consumer].[dbo].[Client] " failed with the following error: "Cannot truncate table 'consumer.dbo.Client' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Hi,
What sql would I use to remove all the nulls in a table?
thanks!
The database has Name,Email, and skill. Though the name is distinct it is repeated as it has different skills. I would like to remove duplicate names and add the corresponding skill to the only one row.
From the stored procedure, combining 3 tables I got the output as:
NameemaildepartmentSkill
ArunemailidTech teamTechnical
ArunemailidTech teamLeadership
ArunemailidTech teamDecision Making
BinayemailidMarketingTechnical
BinayemailidMarketingDecision Making
I would like to remove the duplicate Name fields and combine the Skill in a single row as other fields are same.
So the output should be
NameemaildepartmentSkill
ArunemailidTech teamTechnical, Leadership, Decision Making
BinayemailidMarketingTechnical,Decision Making
I'm trying to remove emails from a table but the simple update query I wrote doesn't seem to do it. I figured I'd test one record instead of the entire dbase. I've probably given more info than needed (the select statement) but wanted to give a frame of reference.
SELECT
Name.ID,
EMAIL,
COMPANY,
MEMBER_TYPE
FROM
APSCU_PROD.dbo.Name
[Code] ....
Hi all,
View 10 Replies View RelatedHi,
Could you please tell me removing table columns with null value will reduce the datafile size.
Data file size needs to be reduced to make free space on the disk, Could please suggest what data type can be choosen to be removed from the existing table so that more space can be made(Char,varhchar,int,date etc )
Thanks
John Jayaseelan
I have a very large table that can contain up 3 to 5 duplicate records. Every month around 100,000 new records come in. Sometimes it's an ammended record, other times is just duplicated by error.
Is it possible to keep the latest record dumped into the table and delete the others? Does SQL track the order of the data being dropped into the table?
The layout would look like this. There are 10-15 other columns in the table where adjustments can also be made.
Lease# Year Month Production
12345 2008 10 1,231
12345 2008 10 1,250
12345 2008 10 1,250
Hello all,
A little question regarding SQL Server DB's.
I have a two tables containing customers invoices, one for the invoices header (ie: customer #, invoice date,... KEY: invoice # + invoice date) and another for the details of the invoices (ie: each invoice line details KEY: invoice # + line #). I need to periodically remove invoices older than a certain timeframe (ex: all invoices older than 48 months).
How can I proceed?
I am fairly new with SQL server.... Please help!
Thanks,
Eric
:(
I have a table and one of the column have junk characters in it, how can I remove the junk characters?.
Eg : Employee
Eid Ename
1 a�
2 �ddd
how can i remove Junk characters and get only Enames from above table.
I need an SQL statement that will hide a field in a column without removing it from the table.
View 3 Replies View RelatedI've look at several different methods for removing leading zero's from a column but I need to remove trailing data from a VARCHAR column. For some reason, the old database saved the time along side the date in my client's app.
For example:
The old database format "2015-07-28 00:00:00"
I need the data in this column in the new database to only be the date "2015-07-28", there are alot of rows with this issue.
Is there a query I can run to remove the 00-00-00 from all of the rows? Some of the fields actually have a time in there like this: 2015-07-28 12:15:35, with this one, I don't think it's going to be easy but if I could at least remove the 00-00-00 from all the rows that have it, that would be a good start.
Hi all,
Report:
-For instance 2 small tables (eg. width 10cm = 3 inch?)
-And one wide table (eg. width 30cm = 10 inch?)
All separated by "insert pagebreak after table".
Problem:
When rendered, the pages with the small tables on have a lot of white blank space at the right of the table. This is probably caused by the big table on page 3.
This report is distributed by email in Excell format. So on sheet 1 and 2 there are a lot of white cells on the right of the tables. When trying to print, they just want to use the "landscape" option and the "fit to page" option. Because of the empty white cells, the fit to page option reduces the first 2 tables to a very small table which covers only 50 % of the page width. The other 50 % is reserved for the empty cells.
Off course, I know that deleting the empty cells offers a solutions, but it would be a lot more handier if there were no empty cells in the first place.
Anybody with a solution?
I need write a query for removing duplicates, for Example in my table I have columns
A_ID name id
1 sam 10
2 sam 10
3 sam 10
4 sam 10
5 ccc 15
6 ccc 15
7 ccc 15
8 fff 20
9 fff 20
10 fff 20
So now I have duplicates values in id column so now I need to take only one value of each and delete the remaining. I need to take first id value 10,15,20 so only 3 rows should be there in my table.
I need removing cursor in my table valued function with alternate code.
ALTER FUNCTION [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer]
(
@containerSqlId SMALLINT,
@containerIncId INT
)
RETURNS @Results TABLE
[Code] ....
declare @error int, @rowcount int
select @rowcount = COUNT(1) FROM STG_BCDR;
while @rowcount > 0
begin
BEGIN TRAN Deletion
[code]....
Above code i try to delete records batch by batch to avoid table locking at BCDR table.total records in this BCDR table is 40,000 records. However I run the code at execution plan, the BCDR table still clustered index scan which means that the locking still happend.
If i change the delete top (5000)...... to delete top (5).... then thre is clustered index seek, which is good..The problem here is each time only delete top 5 records which is means it will realy take very long time to remove those data.
how to cater the situation inorder for me to delete those huge data without table locking happend. If table locking happend , then other user will not be able to access this table at the same time.
Hello...
I was confused with UNIQUE key. Its easy to create PRIMARY KEY to the Table. Now how to alter the Table to have UNIQUE key...
Thanks
Ganesh
Solutions are easy. Understanding the problem, now, that's the hard part
I have made a few table with check constraint
is there anyway that i could retrieve the constraint (Check) conditions for the particular table or columns?
Like in a Query
Inside of stored procedure you have the code
create table #datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
insert into #datatable
exec ('select *
from datatable
where id = 1')
select * from #datatable
I still want to retrieve the data and present it in the software's presentation layer but I still want to remove the temp table in order to reduce performance issue etc.
If I paste the code DROP
TABLE #datatable after
insert into #datatable
exec ('select *
from datatable
where id = 1')
Does it gonna work to present the data in the presentation layer after removing the temp table?
The goal is to retrieve the data from a stored procedure and present it in the software's presentation layer and I still need to remove the temptable after using it because I will use the SP many time in production phase.
[URL]
Hi, all.
I am trying to create table with following SQL script:
Code Snippet
create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) constraint NN_Prj_Name not null,
Creator nvarchar (255),
CreateDate datetime
);
When I execute this script I get following error message:
Error source: SQL Server Compact ADO.NET Data Provider
Error message: Named Constraint is not supported for this type of constraint. [ Constraint Name = NN_Prj_Name ]
I looked in the SQL Server Books Online and saw following:
CREATE TABLE (SQL Server Compact)
...
< column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ] | REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ]
As I understand according to documentation named constraints should be supported, however error message says opposite. I can rephrase SQL script by removing named constraint.
Code Snippet
create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) not null,
Creator nvarchar (255),
CreateDate datetime
);
This script executes correctly, however I want named constraints and this does not satisfy me.
I have a stored procedure that adds constraints from a variable:
ALTER procedure [dbo].[addMyConst](@txtAuto_ourlim money OUTPUT,@txtGen_ourlim money OUTPUT)ASEXECUTE('ALTER TABLE [dbo].[tbl1] ADD CONSTRAINT DF_auto_ourlim DEFAULT ' + @txtAuto_ourlim + ' FOR Auto_ourlim')EXECUTE('ALTER TABLE [dbo].[tbl1] ADD CONSTRAINT DF_Gen_ourlim DEFAULT ' + @txtGen_ourlim + ' FOR Gen_ourlim')
This works fine unless one of the variables is null or empty. Then I get the error: "Incorrect syntax near the keyword 'FOR'
Evidendly SQL Server Express sees "....DEFAULT + + FOR..." but I don't know.
I've fiddled with this a long time and haven't a clue how to fix it. Any help would be appreciated. Thanks, Steve
I made a constraint on a temporary table in a stored procedure but now i can't delete it.
Here's what happened:
I ran this in a stored procedure
CREATE TABLE #TeFotograferen (RowID int not null identity(1,1) Primary Key,Stamboeknummer char(11) ,Geldigheidsdatum datetime, CONSTRAINT UniqueFields UNIQUE(Stamboeknummer,Geldigheidsdatum)
next time i ran the stored procedure it gave me
There is already an object named 'UniqueFields' in the database.
but since the temporary table is out of scope i cannot delete the constraint
I tried
delete from tempdb..sysobjects where name = 'UniqueFields'
and
declare @name
set @name=(SELECT name from sysobjects where id=(Select parent_obj from sysobjects where name='UniqueFields'))
drop table @name
giving me
Ad hoc updates to system catalogs are not allowed.
or
Cannot drop the table '#TeFotograferen__________________________________ __________________________________________________ _________________000000000135', because it does not exist or you do not have permission.
I have a question about a table with triggers or maybe a check constraint.I have the following create tables:
create table bid(
seller char(10) not null,
item_nummer numeric(3) not null,
)
create table Item(
startprice char(5) not null,
description char(22) not null,
start_date char(10) not null,
end_date char(10) not null,
seller char(10) not null,
item_nummer numeric(3) not null,
)
What i'm trying to make is this trigger/constraint: colomn "seller" from table Item will get NULL as long as systemdate is > start_date and end_date, then it will get the value from seller from table bid on the same item_nummer in both table).
Select MemberNbr, EligYear,EligMonth, count(*) FROM [MemberMonth]
GROUP BY MemberNbr, EligYear,EligMonth HAVING COUNT(*) > 1
THIS WILL GIVE ME ZERO ROWS,,, That is GOOD which means NO DUPLICATES..I need to add a constraint to the table MEMBERMONTH to make sure no one can insert duplicate records.
MemberNbr VARCHAR,
EligYear INT ,
EligMonth INT
I have a default constratint on DateColumn getdate()-1
I have used enterprise manager to update it to yesterday's date everyday.
I would like to have a SQL which can check for the date in the system
or even a trigger which checks when the date changes the constraint is updated itself. If this is not possible I would like to have a stored procedure which I will schedule to run as a job everyday once.
So if today 6/12/2006, the default value in the Datecolumn should be
6/11/2006.
This gives me a error, i tried but could not fix the bug.
Alter Table TABLE_NAME
Alter Constraint DF_DATECOLUMN
Default getdate()-1
Ashley Rhodes
Is it possible to create a unique constraint to a column from anothertable? For example:tb_current:current_names--------------aaabbbtb_new:new_name--------cccNow I want to create a constraint on tb_new.new_name to be unique withrespect to tb_current.current_names. However, tb_new.new_name shouldnot be unique to itself. So I should not be able to insert 'aaa' totb_new.new_name. But I should be able to insert 'ccc' totb_new.new_name.Here's the script to reproduce this example:create table tb_current(current_names varchar(10))create table tb_new(new_name varchar(10))insert tb_current values ('aaa')insert tb_current values ('bbb')insert tb_new values ('ccc')select * from tb_currentselect * from tb_newinsert tb_new values ('aaa') -- this should NOT be allowedinsert tb_new values ('ccc') -- this should be allowed
View 3 Replies View RelatedHi
I am trying to update tables "Table A" & " TableB" with a new value for a particular column1. Now "TableA" contains a foreign key "FK1" defined on it and is referencing another table "Table B" containing the similar column "Column1"
Because of this constraint I was not able to update TableA and TableB. My question is
1) If i wanted to go ahead with this update, what are the possible ways?
2) If only solution is to drop the constraint. Then How do i know the definition for this particular Foreign key, i mean it may refer some columns in Table B. I wanted to know the complete definition of it so that i can rebuild the constraint after the update. Right now i can see only the constraint name when i use sp_help TableA. But how do i know the complete reference made by this foreign key.
Early reply is much appreciated
Thanks!
There is a table exists in a database name tbDatabaseProviders.
I want to alter this table to add a column DatabaseProvider Also I want to add a default constraint so if nothing than 1 should be added.
Remember, the table already exists so when I write the script query then I want to first check the constraint if it exists then I dont want to add the constrait otherwise I will add it.
I was having problem to first add a constraint with the constraint name so I could find the constraint before finding it if it already exists.
What should I do the above.
Hi,
I'm trying to figure out how to setup a Constraint (Foreign Key?) from a lookup table.
For the following example, I want to constrain to constrain so that a Car.ParkingSpotNumber is unique for a given ParkingFacility.
Any thoughts?
Thanks!
Andy
Here's the scenario:
CAR table:
Id (PrimaryKey)
LicensePlateNumber
ParkingSpotNumber
PARKINGFACILITY__CAR__LOOKUP table:
Id (PrimaryKey)
CarId (Foreign Key to Car.Id)
ParkingFacilityId (Foreign Key to ParkingFacility)
PARKINGFACILITY table:
Id (PrimaryKey)
PARKINGLOT table:
Id (Foreign Key to ParkingFacility.Id) (PrimaryKey)
Address
PARKINGGARAGE table:
Id (Foreign Key to ParkingFacility.Id) (PrimaryKey)
Address
Here's the T-SQL code to make a SQLServer2005 database for this scenario:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Car]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Car](
[Id] [bigint] NOT NULL,
[CustomerName] [nvarchar](50) NOT NULL,
[ParkingSpotNumber] [int] NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingFacility]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParkingFacility](
[Id] [bigint] NOT NULL,
CONSTRAINT [PK_ParkingFacility] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParkingFacility__To__Car_Lookup](
[Id] [bigint] NOT NULL,
[CarId] [bigint] NOT NULL,
[ParkingFacilityId] [bigint] NOT NULL,
CONSTRAINT [PK_ParkingFacility__To__Car_Lookup] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingLot]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParkingLot](
[Id] [bigint] NOT NULL,
[Address] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ParkingLot] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingGarage]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParkingGarage](
[Id] [bigint] NOT NULL,
[Address] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ParkingGarage] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingFacility__To__Car_Lookup_Car]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]'))
ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] WITH CHECK ADD CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_Car] FOREIGN KEY([CarId])
REFERENCES [dbo].[Car] ([Id])
GO
ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] CHECK CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_Car]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingFacility__To__Car_Lookup_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]'))
ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] WITH CHECK ADD CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_ParkingFacility] FOREIGN KEY([ParkingFacilityId])
REFERENCES [dbo].[ParkingFacility] ([Id])
GO
ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] CHECK CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_ParkingFacility]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingLot_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingLot]'))
ALTER TABLE [dbo].[ParkingLot] WITH CHECK ADD CONSTRAINT [FK_ParkingLot_ParkingFacility] FOREIGN KEY([Id])
REFERENCES [dbo].[ParkingFacility] ([Id])
GO
ALTER TABLE [dbo].[ParkingLot] CHECK CONSTRAINT [FK_ParkingLot_ParkingFacility]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingGarage_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingGarage]'))
ALTER TABLE [dbo].[ParkingGarage] WITH CHECK ADD CONSTRAINT [FK_ParkingGarage_ParkingFacility] FOREIGN KEY([Id])
REFERENCES [dbo].[ParkingFacility] ([Id])
GO
ALTER TABLE [dbo].[ParkingGarage] CHECK CONSTRAINT [FK_ParkingGarage_ParkingFacility]
how can setup constraint for the column in table in database ??
and what will sould write in the (BLOCKED EXPRESSION field ??
I need to run the alter table to drop a default. However, the default name is kind of 'dynamic' from around 1000 databases, thus I need to run the following sql to get the name to a variable. Now, it looks the alter table statement does not like to drop a vaiable, is there a solution about it?
declare @radius_default varchar(40)
select @radius_default = (select sobj.name from sysobjects sobj
inner join syscolumns scolumn
on sobj.ID = scolumn.cdefault
where scolumn.name = 'radius' and sobj.name like '%LandMarks%')
print 'Need To Drop @radius_default: ' + @radius_default
--====================================
alter table LandMarks drop constraint @radius_default
thanks
David
Create table enrollment_in
BEN_DENT VARCHAR(1)
What I need is a constraint to limit the values to Y or N