I.o. StockItems think Parcels.

Will this do?

A Transaction can be a Purchase. In that case a new Parcel is created.

For consistency reasons, I put all my FK nullable.

CREATE TABLE dbo.Transactions
	(
	Transactions_Id int identity(1,1)  NOT NULL,
	Transactiontypes_id int null,
	Parcels_Id int null,
	TransactionDate date not null,
	D010Transactiontype char(3) not null default N'NA',
	D010Transaction nchar(7) not null default N'NA',
	D020Transactiontype nchar(3) not null default N'NA',
	D020Transaction nchar(5) not null default N'NA',
	Goods nchar(1) not null,
	TransactionWeight decimal(18,2) not null,
	Amount decimal (18,2),
	AmountSold decimal (18,2),
	Creationdate datetime not null ,
	Lastupdatedate datetime default getDate()
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Transactions ADD CONSTRAINT
	PK_Transactions PRIMARY KEY CLUSTERED 
	(
	Transactions_Id
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]



CREATE TABLE dbo.Parcels
	(
		Parcels_Id int NOT NULL,
		Origins_Id int NULL,
		CreationTransaction_Id int NULL,
		DestinationTransaction_Id int NULL,
		D020number nchar(8) NOT NULL default N'NA',
		D020wrapper nchar(8) NOT NULL default N'NA',
		Goods nchar(1) NOT NULL,
		ParcelWeight decimal(18, 2) NOT NULL,
		Cost decimal(18, 2) NOT NULL default 0,
		InventoryCalculationstatus nchar(1) NOT NULL,
		Sold decimal(18, 2) NOT NULL default 0,
		ParcelsStatus nchar(1) NOT NULL,
		Creationdate datetime not Null,
		Lastupddatedate datetime default getDate()
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Parcels ADD CONSTRAINT
	PK_Parcel PRIMARY KEY CLUSTERED 
	(
	Parcels_id
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

alter table dbo.Transactions 
	with check 
	add constraint FK_Transactions_Parcels 
	Foreign key (Parcels_id) 
	references Dbo.Parcels (Parcels_id)
alter table dbo.Transactions
	with check 
	add constraint FK_Transactions_Transactiontypes 
	Foreign key (Transactiontypes_id) 
	references dbo.Transactiontypes (Transactiontypes_id)
alter table dbo.Parcels
	with check 
	add constraint FK_Parcels_CreationTransaction_id 
	Foreign key (CreationTransaction_id) 
	references dbo.Transactions (Transactions_id)
alter table dbo.Parcels
	with check 
	add constraint FK_Parcels_DestinationTransaction_id 
	Foreign key (DestinationTransaction_id) 
	references dbo.Transactions (Transactions_id)
alter table dbo.Origins
	with check
	add constraint FK_Origins_Parcels_id
	foreign key (Parcels_id)
	references dbo.Parcels (Parcels_id)

>>Assuming a StockItems Table and a Transactions Table. If I want the Transactions Table to have a Foreign Key into the StockItems, and the StockItems to have a foreign keys into Transactions, there's no way I will be able to add a record in both tables (some Transactions will add StockItems) during the same transaction if I make both Foreign Keys non nullable.
>>
>>a) Is there?
>>b) Is it ok the set foreign keys nullable?
>>c) Is it not best practice to cross reference like this?
>>d) is there anything else wrong in my reasoning?
>
>
>Someone might jump in with a reply, but I want to ask this to confirm.
>
>Can you post the table structure for both tables, and confirm what your PK/FK relationships are?
>
>Thanks