Hot answers tagged referential-integrity
12
Oracle:
Since Oracle doesn't index entries where all indexed columns are null, you can use a function-based unique index:
create table foo(bar integer, chk char(1) not null check (chk in('Y', 'N')));
create unique index idx on foo(case when chk='Y' then 'Y' end);
This index will only ever index a single row at most.
Knowing this index fact, you can ...
9
SQL Server:
How to do it:
The best way is a filtered index. Uses DRI
SQL Server 2008+
Computed column with uniqueness. Uses DRI
See Jack Douglas' answer. SQL Server 2005 and before
An indexed/materialised view which is like a filtered index. Uses DRI
All versions.
Trigger. Uses Code, not DRI.
All versions
How not to do it:
Check constraint with a ...
9
I think this is a case of structuring your database tables correctly. To make it more concrete, if you have a person with multiple addresses and you want one to be the default, I think you should store the addressID of the default address in the person table, not have a default column in the address table:
Person
-------
PersonID
Name
etc.
DefaultAddressID ...
7
In Oracle, one way to enforce this sort of constraint in a declarative fashion would be to create a materialized view that is set to refresh fast on commit whose query identifies all the invalid rows (i.e. BookAspectRating rows that have no match in BookAspect_view). You can then create a trivial constraint on that materialized view that would be violated ...
5
Possible approaches using widely implemented technologies:
1) Revoke 'writer' privileges on the table. Create CRUD procedures that ensure the constraint is enforced at transaction boundaries.
2) 6NF: drop the CHAR(1) column. Add a referencing table constrained to ensure its cardinality cannot exceed one:
alter table foo ADD UNIQUE (bar);
create table ...
5
This business rule can be enforced in the model using only constraints. The following table should solve your problem. Use it instead of your view:
CREATE TABLE BookAspectCommonTagLink
( BookID INT NOT NULL
, AspectID INT NOT NULL
, TagID INT NOT NULL
--TagID is deliberately left out of PK
, PRIMARY KEY (BookID, AspectID)
, FOREIGN ...
5
This kind of problem is another reason why I asked this quiestion:
Application Settings in Database
If you have an application setting table in your database you could have an entry that would reference the ID of the one record you want to be considered 'special'. Then you would just look-up what the ID is from your settings table, in this way you dont ...
4
The restrictions on truncating tables include:
You cannot truncate the parent table of an enabled foreign key
constraint. You must disable the constraint before truncating the
table. An exception is that you can truncate the table if the
integrity constraint is self-referential.
This is presumably because truncate is DDL and doesn't do any checks ...
4
No, there is no value in adding an additional "primary key" to this table. Your joins are only ever going to refer to ProducerID and ProductID, so it is just dead weight. IMHO.
Though I agree with @Shark that the join table doesn't even seem to be needed here, unless you are going out of your way to not change the schema of the existing tables in any way.
...
4
Your table will work fine for this purpose, but you probably want to add an index. If the primary reason for using this table is to take an outside_ticket_id and get the corresponding ticket_id's I would add the following clustered index:
CREATE CLUSTERED INDEX [CL_Lookup_OD_ID] on [lookup](outside_data_id)
GO
If the primary lookup will be the other way ...
4
A structure like yours should probably be solved with:
- a multi-column primary key constraint on the m-table (lookup) and
- a foreign key constraint referencing the primary key of the 1-table.
An optimal index for looking up values in one direction is provided automatically by the primary key of the lookup table.
CREATE TABLE ticket (
ticket_id integer ...
4
The simplest approach is to store each relationship exactly once, and enforce that with a check constraint Friend1
CREATE VIEW AllFriendships
AS
SELECT Friend1, Friend2 FROM Friendships
UNION ALL
SELECT Friend1 AS Friend2, Friend2 AS Friend1 FROM Friendships
If, however, you really need the table with both Friend1,Friend2 and Friend2,Friend1, you could ...
3
According to IBM's documentation: "Typically, you need to manually perform integrity processing for a table in three situations: After loading data into a table; when altering a table by adding constraints on the table; and when altering a table to add a generated column."
That would be the why you need to do it. We use it right after we use the LOAD ...
3
I imagine that most sports leagues don't change their hierarchy of conference, division, etc... very often so it's probably safe to have each level in its own table.
If your goal is a system that can handle different leagues that have different hierarchy depths, then the recursive table approach is probably better.
I don't know how easy it would be to do ...
3
For those who use MySQL, here is an appropriate Stored Procedure:
DELIMITER $$
DROP PROCEDURE IF EXISTS SetDefaultForZip;
CREATE PROCEDURE SetDefaultForZip (NEWID INT)
BEGIN
DECLARE FOUND_TRUE,OLDID INT;
SELECT COUNT(1) INTO FOUND_TRUE FROM PostalCode WHERE isDefault = TRUE;
IF FOUND_TRUE = 1 THEN
SELECT ID INTO OLDID FROM PostalCode ...
3
The structure you've created seems like a perfectly reasonable lookup table.
CREATE TABLE lookup1
(
outside_data_id integer NOT NULL
, ticket_id integer NOT NULL
);
I would probably add an index to this table like:
CREATE CLUSTERED INDEX IX_Lookup1
ON Lookup1 (outside_data_id, ticket_id)
WITH (ALLOW_PAGE_LOCKS=ON, ALLOW_ROW_LOCKS=ON, ...
3
Personally I would put the TimePoint attributs into the event table. But if you prefer two tables, you should be able to achieve that with foreign keys together with NOT NULL columns.
But that will only work if you have a database that supports deferrable constraints (Oracle and PostgreSQL come to mind), otherwise there is no way of inserting new values ...
3
Arguments can be made either way depending on whether you want to handle populating these tables using triggers or the application(s).
I think this depends on what exactly you want to store in these logging tables, if you're logging anything specific to your 3 "data" tables then you'd be better off with 3 logging tables (1 logging table per "data" table ...
3
Run this command : SHOW CREATE TABLE log_url\G
This will give you a display of the SQL needed to create the table.
It will also show you the indexes and constraints for that table.
The first index (key 1) in that table must have a column called url_id.
From the error message, you are being told that there exists a row in log_url that has 41721 already ...
2
In SQL Server 2000 and over you can use Indexed Views to implement complex (or multi-table) constraints like the one you're asking for.
Also Oracle has a similar implementation for materialized views with deferred check constraints.
See my post here.
2
Been a while since I've worked in sybase but (from memory) the following sql should point you in the right direction:
select *
from sysobjects so inner join syscolumns sc on so.id = sc.id
where sc.name = 'field name'
you can also check out the sybase books online (system tables)
2
If you have a one-to-many relationship between Producers and Products (in other words, a product can only belong to one producer), than it would make sense to just put a foreign key reference directly in your Products table:
One-To-Many
create table Producer
(
id int identity(1, 1) not null primary key clustered,
Name varchar(100) not null
)
go
...
2
For OneDayWhen:
Assuming the Product and Order_Detail tables:
create table PRODUCT
(
PRODUCT_ID NUMBER(38,0) not null,
PRODUCT_NAME VARCHAR2(100) not null,
PRODUCT_DESCR VARCHAR2(4000) null,
ACTIVE_FLAG NUMBER(1,0) DEFAULT 1 not null
);
create table ORDER_DETAIL
(
ORDER_DETAIL_ID ...
2
Suggestions:
Disallow deletion of a product that has been ordered.
Move the order detail row to an archive table that has no foreign key to products (perhaps denormalized to store product name etc that would otherwise be lost) then delete the product.
Move the product to an archive table and the order detail to another archive table with a foreign key ...
2
Typically what you'd do in this scenario is, instead of deleting the products, just set a bit (or use an int type field if there could be more than 2 options) to denote that it is "deleted", or inactive.
create table Product
(
Id int identity(1, 1) not null,
Name varchar(100) not null,
Description varchar(1000) null,
IsActive bit not null ...
2
If my memory serves me well, 'One to either' is called Polymorphic Association. Usually, it can be resolved by using a common parent table (index definitions are skipped for simplicity):
PortfolioParent (id int not null PRIMARY KEY,
portfolio_type char(1) not null,
CONSTRAINT UQ_PORTFOLIO_PARENT UNIQUE (id,portfolio_type),
-- unique constraint may seem ...
2
Standard Transitional SQL-92, widely implemented e.g. SQL Server 2000 and above:
Revoke 'writer' privileges from the table. Create two views for WHERE chk = 'Y' and WHERE chk = 'N' respectively, including WITH CHECK OPTION. For the WHERE chk = 'Y' view, include a search condition to the effect that its cardinality cannot exceed one. Grant 'writer' ...
2
All you need is to propagate TypeId down to the link table:
CREATE TABLE Commands(
CommandId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL,
DeviceTypeId int NOT NULL REFERENCES DeviceTypes(TypeId),
CONSTRAINT UniqueCommandNameInSpecificType UNIQUE(Name, DeviceTypeId),
-- added this constraint
CONSTRAINT ...
2
The basic idea is to separate hierarchy of an organizational chart from actuals teams.
The Level table looks like
LevelID | LevelType
-------------------
1 League
2 Division
3 Conference
4 Team_Slot
Team_Slot is a "pigeon-hole" for an actual team
to fill-in over time.
Few more notes:
Alternate Key (PositionID, LevelID) ...
Only top voted, non community-wiki answers of a minimum length are eligible
