I have a SQLServer with a linked server onto another database somewhere else. I have created a view on that linked server create view vw_foo as select [id], [name] from LINKEDSERVER.RemoteDatabase.dbo.tbl_bar I'd like to to the following alter table [baz] add foo_id int not null go alter table [baz] with check add constraint [fk1_...
I've been tasked with investigating licence server vendors. (Note: licence servers, or "key servers", are a type of "software copy protection" wherein copies of software deployed to a site call a central licence server to see if they're allowed to run. This is more than just a licence key generation/verification algorithm!) We need a l...
Nearly identical to http://stackoverflow.com/questions/85978/query-a-tables-foreign-key-relationships, but for SQL Server 2000 For a given table 'foo', I need a query to generate a set of tables that have foreign keys that point to foo. ...
In SQL Server 2008 and given TableA(A_ID, A_Data), TableB(B_ID, B_Data), and ViewC(A_or_B_ID, A_or_B_Data), is it possible to define TableZ(A_or_B_ID, Z_Data) such that Z's A_or_B_ID column is constrained to the values found in ViewC? Can this be done with a foreign key against the view? ...
On MS SQL Server 2005, I have a table that I want to replace. Many other tables have a Foreign Key reference to this table. Is there a script that I can run to simply roll over all these references from the old table to the new table? Or am I going to have to go through and specifically drop each of these foreign keys, and write a new...
I am generating a data template in C#. With the help of the good people on this wonderful site, I've managed to take care of almost every issue. This should be the last problem. Because it's a template I'm working on, I want every field in the table, including nulls. I was helped on how to update nulls by adding (object)this.field ?? DBN...
I have two related clases JPA-annotated. Alarm and Status. One Alarm can have one Status. What I need is to be able to delete one Status and "propagate" a null value to the Alarms that are in that Status that has been deleted. That is, I need the foreign key to be defined as "on delete set null". @Entity public class Alarm { @Id...
So far the association works fine (the User class loads the appropriate UserRoles instance when present), but when creating a new User and setting its Roles property to a new instance of UserRoles, the UserRoles object is not saved. Here is my abridged User.hbm.xml: <?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:...
I misremembered what the key was for this Templates table and therefore I added the wrong field as a foreign key. Now I need to add the foreign key and I want to populate its values based on this other field that is already populated. I started trying to do this with an update statement, but I'm not sure how to do it. Part of my schema:...
Is there a way to reserve or skip or increment value of identity column? I Have two tables joined in one-to-one relation ship. First one has IDENTITY PK column, and second one int PK (not IDENTITY). I used to insert in first, get ID and insert in second. And it works ok. Now I need to insert values in second table without inserting in...
I've got the following 2 tables: ingredients (id, name) ingredient_prices (id, ingredient_id, price, created_at) such that one ingredient can have many prices. What will be the best way to get the latest entered price per ingredient? I know it can be done easily with sub-selects, but I want to know if there is a more efficient way fo...
Is it possible via script/tool to generate a delete statement based on the tables fk relations. i.e. I have the table: DelMe(ID) and there are 30 tables with fk references to its ID that I need to delete first, is there some tool/script that I can run that will generate the 30 delete statements based on the FK relations for me ? (btw I...
I have a User class with reference to a Message class. The message class has a user_id (which is the sender) and a receiver_id. So in the User class I have has_many :messages has_many :messages, :foreign_key => "receiver_id" and then in the Message class I have belongs_to :user The first relationship -- via user_id -- goes p...
Hello Consider this example schema: Customer ( int CustomerId pk, .... ) Employee ( int EmployeeId pk, int CustomerId references Customer.CustomerId, .... ) WorkItem ( int WorkItemId pk, int CustomerId references Customer.CustomerId, null int EmployeeId references Employee.EmployeeId, .... ) Basical...
Suppose I have 2 tables, Products and ProductCategories. Both tables have relationship on CategoryId. And this is the query. select p.ProductId, p.Name, c.CategoryId, c.Name AS Category from Products p inner join ProductCategories c on p.CategoryId = c.CategoryId where c.CategoryId = 1; When I create execution plan, table ProductCateg...
I have a database of components. Each component is of a specific type. That means there is a many-to-one relationship between a component and a type. When I delete a type, I would like to delete all the components which has a foreign key of that type. But if I'm not mistaken, cascade delete will delete the type when the component is dele...
Why does the SQL Standard accept this? Which are the benefits? If have those tables: create table prova_a (a number, b number); alter table prova_a add primary key (a,b); create table prova_b (a number, b number); alter table prova_b add foreign key (a,b) references prova_a(a,b) ; insert into prova_a values (1,2); You can insert thi...
I need a tool or method that allows the transfer of data and automatically updates the foreign keys at the destination table. The SET IDENTITY_INSERT ON/OFF is not what I'm looking for. Example: table master (id int identity, name char) table slave (id int identity, master_id int, name char) I would like to create a script like thi...
Hi, I am trying to have 2 tables with crossed foreign keys, but I am not allowed to reference a table that doesn't exist when I am creating them. Any way of creating tables like this for mysql, something like declare both tables at the same time or delay evaluation of foreign keys? Error is 1005: Can't create table blocks.frm (errno 15...
In LINQ to SQL, is InsertOnSubmit() required when adding via a Foreign Key? I.e. If I have code that does the following, where orders and order lines are linked in the dbml and database via a foreigh key: Create order. Add order to datacontext orders. Create order line. Add to order.Lines. Do I still need to add the order line to th...