|
Ok sweet ill look into that.
Thank you!
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
Click here to view my blog
|
|
|
|
|
No problem,you are welcome.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Not sure if that was intentionally evil but TRUNCATE will remove ALL the data from the table...
The reason you are getting the error is because SQL Server is enforcing referential integrity. It's easiest to explain in the context of a simple example, so imagine you have book and author tables, and for the sake of simplicity let's assume a book can have only one author, but an author can write many books. The way this would be implemented in a relational database is by defining a foreign key column in the Books table that point to an author, say AuthorID. Now if you delete an Author there may be Book records that refer to that author, and this is generally considered a bad thing. Depending on your scenario it might make sense to decide that if an author is deleted, so should all the books by that author be. This is called CASCADE DELETE and can be configured in SQL Server so your code can just delete one or more Author records, leaving it to SQL Server to also delete the related books. In other cases it might make more sense to UPDATE the Books table and perhaps set NULL in the AutorID column, or sometimes even to transfer the association so it refers to another entity (though in our example it makes no sense to change the author of books).
SQL server only enforces referential integrity for relations that are so configured. You CAN change the setup and it will allow you to leave data referring to non-existing data, but of course this is seldom a good idea. Usually it is best to define all your relations in the database and enforce integrity everywhere. This will help you spot errors much earlier in the development and prevent errors in your code from corrupting the data, but may of course cause blocking bugs if you do not perform adequate testing.
|
|
|
|
|
Hi,
If you are deleting row from a table which has primary key referenced to other table as foreign key,
Solution 1 - you will have to delete foreign key occurence of the data first and then delete primary key occurence or you'll get reference error which I think you are facing currently,
Solution 2 - uou can force referential integrity between relations this will delete all forieign occurences on deletion of primary key but this solution is not recommended.
Note: Truncate will delete all the record of your table and I'm not sure this will solve your problem anyways becuse truncate operation also will give referential error.
hope this solves your problem
Bi
|
|
|
|
|
razov wrote: Solution 1 - you will have to delete foreign key occurence of the data first and then delete primary key occurence or you'll get reference error which I think you are facing currently,
Yes, this is my problem.
Is there script or something I can execute to search for al these foreign keys (or are they listed somewhere)?
The problem is I do not know where they all are!
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
Click here to view my blog
|
|
|
|
|
For foreign key dependencies either you can look in th DB documentation(if any) or create a DB Diagram.
Here is one query using the INFORMATION_SCHEMA views that returns both sides of all FOREIGN KEY relationships, as well as the name of the foreign key constraint.
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
ORDER BY
1,2,3,4
If you want to limit it to specific tables, you can add any of the following immediately prior to the optional ORDER BY clause:
WHERE PK.TABLE_NAME='something'
WHERE FK.TABLE_NAME='something'
WHERE PK.TABLE_NAME IN ('one_thing', 'another')
WHERE FK.TABLE_NAME IN ('one_thing', 'another')
Hope this solves your problem.
|
|
|
|
|
Thankyou very much!
I'll play with this tommorrow at work.
Cheers,
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
Click here to view my blog
|
|
|
|
|
|
thank god sql not allow you delete the records!
if it let´s you do that how will you handle it? you are not supose to do that!
it won´t let you delete because it´s using those record in other table.
do this:
if not exists(select * from other_table where id_other_table = @id_contact) begin
delete from contact where id_contact = @id_contact
select 'OK: deleted'
end
else
begin
select 'ERROR:inform that you can delete that row because you use it in other_table'
end
that way you know sure where in which table the record is used!
go to that table and delete then afterwords if you want
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
should care the order in every group
for example,
table:
id data num
1 2008-1-1 2
2 2008-2-2 2
3 2008-1-1 1
4 2008-2-2 4
5 2008-1-1 5
the result will be:
data first last
2008-1-1 2 5
2008-2-2 2 4
Here in every group take first/last in the order of id asc.
system
|
|
|
|
|
select distinct data,<br />
(select top 1 (t1.num) from myTable as t1 where t1.data = myTable.data order by t1.id ) as firstRow,<br />
(select top 1 (t2.num) from myTable as t2 where t2.data = myTable.data order by t2.id desc) as LastRow<br />
from myTable
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I got a problem because myTable is a sub-query, so I can't have 2 alias names.
How to deal with it?
Thanks.
system
|
|
|
|
|
What is your real table name?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
select top 1 (t2.num) from myTable as t2 where t2.data = myTable.data order by t2.id desc
Now I want to consider the record whose ID is above 10, so I shall use
(SELECT * FROM myTable WHERE ID>10)
to replace myTable in the above SQL statement.
But lack a alias name, how to write it?
system
|
|
|
|
|
Here is the table for query:
date(PK) value
2008-1-1 5
2008-2-1 10
...
Now sort the table by date ascendingly,
and make every 10 record a group
(the last group may catain less than 10 records),
I want to query last record in every group.
system
|
|
|
|
|
hi, i need to create a stored procedure with a Count(*) option and also an output parameter with the Id of the selected record. IS it possible?
|
|
|
|
|
Yes
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Well the question does not make sense:
ID infers a unique identifier
Count(*) requires a group by
So your question is can I group by a unique identifier. Try rephrasing the question (I think you just got it wrong) and give us some more detail.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Why do you use Count(*), it is better to use Count(ID)
ID infers a unique identifier in that table
CREATE PROCEDURE titles_count @@TITLE varchar(40) = '%', @@count int OUTPUT
AS
SELECT @@count = count(title_id)
FROM titles
WHERE title LIKE @@TITLE
GO
Venky
|
|
|
|
|
Why is it better to use count(field) instead of count(*), I have never heard that there is a difference in cost
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I want to import data to Microsoft Sql Server 2005 from .csv file
In .csv file I am having a column which has numeric data . During the import process I am receiving data conversion error.
In case I used this column as string then it works, but I want column as numeric.
Kindly tell if I am missing something.
Thanks in Advanced.
|
|
|
|
|
a_b111 wrote: which has numeric data . During the import process I am receiving data conversion error
Either you have a non-numeric value, or your numeric data type does not match that of your file. Check all your values against your data types.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
As Bob said - data types, probably a blank cell that is treated as "".
I always import into a staging table of varchars and use a stored proc to do the transforms AFTER loading the data. Dates will also screw you with weird formats.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have tried to import into a staging table of varchar and use a query to do the transforms AFTER loading the data, but it failed.
Please tell me the stored procedure to do the transforms AFTER loading the data.
|
|
|
|
|
a_b111 wrote: Please tell me the stored procedure to do the transforms AFTER loading the data.
You have to write it, in your OP you said you could get it to work using string - varchar so that part should work.
Now you have the data in front of you and you know where it has to go - so write the procedure to do the work. This removes the LOAD from the potential problems you can have, now all you need to do is transform the data into your tables.
Never underestimate the power of human stupidity
RAH
|
|
|
|